如何计数项目使用generate_series()在MySQL等价? [英] How to count items using generate_series() equivalent in MySQL?
问题描述
我有一个Postgresql数据库中的用户列表,我想计算每个字母有多少用户。
这里是SQL查询: p>
选择chr(chars.letter + ascii('A'))作为字母,
count(m.nome)
来自generate_series(0,25)as chars(letter)
left join merchant m on ascii(left(m.nome,1))= chars.letter + ascii('A')
按字母
按字母顺序排列
(感谢
i have a list of users in my Postgresql db and i want to count how many users there are for every letter.
Here is the SQL query:
select chr(chars.letter + ascii('A')) as letter,
count(m.nome)
from generate_series(0, 25) as chars(letter)
left join merchant m on ascii(left(m.nome, 1)) = chars.letter + ascii('A')
group by letter
order by letter asc
(thanks to this answer)
Here is the result in PHPPGAdmin:
Now since i migrate to MySQL i need to use the same query but it seems that MySQL doesn't use generate_series(). So, how to get the same result?
So lets assume you have some table with at least 26 records in it (maybe information_schema.columns
perhaps?).
The following will generate all uppercase alphabetical letters:
SET @c := 64;
SELECT CAST(CHAR(@c := @c + 1) AS CHAR(1)) AS letter
FROM table_with_at_least_26_rows
LIMIT 26
;
To embed the above into your original query, put the SET @c := 64;
before the query, then replace generate_series(0, 25) as chars(letter)
with ( SELECT CAST ... LIMIT 26 ) chars
. Be sure to include the parentheses as it will make the query into a subquery.
SQL Fiddle of the query: http://sqlfiddle.com/#!9/6efac/8
这篇关于如何计数项目使用generate_series()在MySQL等价?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!