sql - 汇总 SQL Server 中 1 列中每个值的值计数

Question

数据：

name | car color
-----+---------
mike | green
mike | blue
mike | red
bob  | green
bob  | red
tom  | blue

期望的结果：

name | green count | blue count | red count
-----+-------------+------------+---------
mike |      1      |      1     |    1
bob  |      1      |      0     |    1
tom  |      0      |      1     |    0

我正在考虑运行几个子查询并离开加入，例如：

select name, count(*) as count blue
from table
where car color = 'blue'

ETC

谢谢

标签： sqlsql-servertsqlgroup-bypivot

Answer 1

您可以使用条件聚合：

SELECT 
    name,
    SUM(CASE WHEN car_color = 'green' THEN 1 ELSE 0 END) green_count,
    SUM(CASE WHEN car_color = 'blue' THEN 1 ELSE 0 END) blue_count,
    SUM(CASE WHEN car_color = 'red' THEN 1 ELSE 0 END) red_count
FROM mytable 
GROUP BY name