How to count items using generate_series() equival

2019-08-03 09:45发布

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:

enter image description here

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?

2条回答
爱情/是我丢掉的垃圾
2楼-- · 2019-08-03 10:06

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

查看更多
对你真心纯属浪费
3楼-- · 2019-08-03 10:15

Here is the final solution:

SELECT
   LEFT(name, 1) AS first_letter,
   COUNT(*) AS total
FROM users
GROUP BY first_letter

Source

查看更多
登录 后发表回答