+----------+--------+
| name | version|
+----------+--------+
| book | 2 |
| book | 1 |
| book | 1 |
| pen | 1 |
| pen | 2 |
| pen | 2 |
| pen | 2 |
| paper | 1 |
+----------+--------+
我有上面的表，我想对按名称分组和按版本计数（行）结果进行查询:
+----------+--------+--------+
| name | version| count |
+----------+--------+--------+
| book | 1 | 2 |
| book | 2 | 1 |
| pen | 1 | 1 |
| pen | 2 | 3 |
| paper | 1 | 1 |
| paper | 2 | 0 |
+----------+--------+--------+
查询将是
SELECT name, version, count(*) as count
FROM your_table_name
GROUP BY name, version
如果希望所有名称/版本组合，则使用交叉联接生成所有行，然后使用左联接引入现有数据:
select n.name, v.version, count(t.name)
from (select distinct name from t) n cross join
(select distinct version from t) v left join
t
on t.name = n.name and t.version = v.version
group by n.name, v.version
order by n.name, v.version;
