如何在Postgres中创建带有别名的嵌套SELECT COUNT [英] How to create nested SELECT COUNT with alias in Postgres

问题描述

我为我的Postgres数据库写下面的SQL查询：

i'm writing the following SQL query for my Postgres database:

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%')
)

输出是一个名为row的列，包含以下内容：

The output is one column named "row", with the following content:

(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等等...根据我的别名）与相关的总计。为什么它给了我一行？

I should get 26 rows (named "A", "B", and so on... according to my alias) with the related total. Why does it give me one row?

如果我通过PHP var_dump 读取它的输出如下： / p>

If i read it through PHP var_dump the output is the following:

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)"

有什么问题？

推荐答案

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

You want to create a separate row for each character. One way is to generate all the characters and then aggregate by them. Here is one approach:

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;

编辑：

Alan的建议是更好的查询：

Alan's suggestion is a better query:

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;

这篇关于如何在Postgres中创建带有别名的嵌套SELECT COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！