SQL group by 和 count 固定列值

Question

我在 SQL(MySQL) 中的数据导入脚本中遇到问题，我需要按类型对行进行分组以计算每种类型有多少行。到目前为止，这并不是一个真正的问题，因为我知道我可以做到:

SELECT 
  data.type, 
  COUNT(data.type) 
FROM data 
GROUP BY data.type;

所以，通过这样做，我得到了结果:

 -------------- --------------------- | type         | COUNT(data.type)    ||--------------|---------------------|| 0            |                   1 || 1            |                  46 || 2            |                  35 || 3            |                 423 || 4            |                  64 || 5            |                  36 || 9            |                   1 | -------------- ---------------------

I know that in the type column the values will always be in the range from 0 to 9, like the above result. So, I would like to list not only the existing values in the table content but the missing type values too, with their COUNT value set to 0.

Based on the above query result, the expected result would be:

 -------------- --------------------- | type         | COUNT(data.type)    ||--------------|---------------------|| 0            |                   1 || 1            |                  46 || 2            |                  35 || 3            |                 423 || 4            |                  64 || 5            |                  36 || 6            |                   0 || 7            |                   0 || 8            |                   0 || 9            |                   1 | -------------- --------------------- 

I could trickly INSERT one row of each type before GROUP/COUNT-1 the table content, flagging some other column on INSERT to be able to DELETE these rows after. So, the steps of my importation script would change to:

1. TRUNCATE table; (I can't securily import new content if there were old data in the table)
2. INSERT "control" rows;
3. LOAD DATA INFILE INTO TABLE;
4. GROUP/COUNT-1 the table content;
5. DELETE "control" rows; (So I can still work with the table content)
6. Do any other jobs;

But, I was looking for a cleaner way to reach the expected result. If possible, a single query, without a bunch of JOINs.

I would appreciate any suggestion or advice. Thank you very much!

EDIT

I would like to thank for the answers about CREATE a table to store all types to JOIN it. It really solves the problem. My approach solves it too, but does it storing the types, as you did.

So, I have "another" question, just a clarification, based on the received answers and my desired scope... is it possible to reach the expected result with some MySQL command that will not CREATE a new table and/or INSERT these types?

I don't see any problem, actually, in solve my question storing the types... I just would like to find a simplified command... something like a 'best practice'... some kind of filter... as I could run:

GROUP BY data.type(0,1,2,3,4,5,6,7,8,9)

它可以返回这些过滤后的值。
如果真的存在/可能的话，我真的很想学习这样的命令。
再次，非常感谢你!

Answer 1

假设您有一个 types包含所有有效类型的表:

SELECT t.type, 
       COUNT(data.type) 
FROM data join types t on data.type = t.type
GROUP BY t.type
order by t.type

您应该包含显式 order by而不依赖于 group by以特定顺序产生结果。