如何在Postgres中使用别名创建嵌套的SELECT COUNT

admin

如何在Postgres中使用别名创建嵌套的SELECT COUNT

sql

我正在为我的Postgres数据库编写以下SQL查询：

SELECT(
(SELECT count(*) as A FROM merchant WHERE nome LIKE 'A%'),
(SELECT count(*) as B FROM merchant WHERE nome LIKE 'B%'),
(SELECT count(*) as C FROM merchant WHERE nome LIKE 'C%'),
(SELECT count(*) as D FROM merchant WHERE nome LIKE 'D%'),
(SELECT count(*) as E FROM merchant WHERE nome LIKE 'E%'),
(SELECT count(*) as F FROM merchant WHERE nome LIKE 'F%'),
(SELECT count(*) as G FROM merchant WHERE nome LIKE 'G%'),
(SELECT count(*) as H FROM merchant WHERE nome LIKE 'H%'),
(SELECT count(*) as I FROM merchant WHERE nome LIKE 'I%'),
(SELECT count(*) as J FROM merchant WHERE nome LIKE 'J%'),
(SELECT count(*) as K FROM merchant WHERE nome LIKE 'K%'),
(SELECT count(*) as L FROM merchant WHERE nome LIKE 'L%'),
(SELECT count(*) as M FROM merchant WHERE nome LIKE 'M%'),
(SELECT count(*) as N FROM merchant WHERE nome LIKE 'N%'),
(SELECT count(*) as O FROM merchant WHERE nome LIKE 'O%'),
(SELECT count(*) as P FROM merchant WHERE nome LIKE 'P%'),
(SELECT count(*) as Q FROM merchant WHERE nome LIKE 'Q%'),
(SELECT count(*) as R FROM merchant WHERE nome LIKE 'R%'),
(SELECT count(*) as S FROM merchant WHERE nome LIKE 'S%'),
(SELECT count(*) as T FROM merchant WHERE nome LIKE 'T%'),
(SELECT count(*) as U FROM merchant WHERE nome LIKE 'U%'),
(SELECT count(*) as V FROM merchant WHERE nome LIKE 'V%'),
(SELECT count(*) as W FROM merchant WHERE nome LIKE 'W%'),
(SELECT count(*) as X FROM merchant WHERE nome LIKE 'X%'),
(SELECT count(*) as Y FROM merchant WHERE nome LIKE 'Y%'),
(SELECT count(*) as Z FROM merchant WHERE nome LIKE 'Z%')
)

输出是名为“行”的一列，其内容如下：

(26,20,28,13,15,9,13,16,13,1,0,13,20,7,10,20,0,17,44,25,3,8,7,1,2,2)

我应该得到26行（根据我的别名命名为“ A”，“ B”，依此类推…），并包含相关的总数。为什么给我一排？

如果我通过PHP读取，var_dump则输出如下：

string(68) "(26,20,28,13,15,9,13,16,13,1,0,13,20,7,10,20,0,17,44,25,3,8,7,1,2,2)"

怎么了？我是在犯任何错误还是与Postgres有关？

阅读 217

2021-06-07

共1个答案

admin

您要为每个字符创建一个单独的行。一种方法是生成所有字符，然后由它们聚合。这是一种方法：

select chr(chars.c + ascii('A')) as c,
       sum(case when ascii(left(m.nome, 1)) = chars.c + ascii('A') then 1 else 0 end)
from generate_series(0, 25) as chars(c) cross join
     merchant m
group by c;

编辑：

艾伦的建议是一个更好的查询：

select chr(chars.c + ascii('A')) as c,
       count(m.nome)
from generate_series(0, 25) as chars(c) left join
     merchant m
     on ascii(left(m.nome, 1)) = chars.c + ascii('A')
group by c;

2021-06-07