sql server中统计多个互斥数据的数量

2019独角兽企业重金招聘Python工程师标准>>>

创建表

CREATE TABLE [dbo].[color](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[color] [varchar](50) NULL
) ON [PRIMARY]

 随机插入数据 

id	color
1	green
2	red
3	blue
4	green
5	red
6	red

统计每种 颜色的数量

当需要使用一个语句查询每种颜色的数量时，不能把颜色条件放进WHERE条件中, 因为每种颜色是互斥的。

red	blue	green
3	1	2

下面SQL可得到上面的结果：

SELECT
    SUM(CASE WHEN(color = 'red') THEN 1 END) AS red,
    SUM(CASE WHEN(color = 'blue') THEN 1 END) AS blue,
    SUM(CASE WHEN(color = 'green') THEN 1 END) AS green
  FROM [dbo].[color]

或者

SELECT
    COUNT(CASE WHEN(color = 'red') THEN 1 END) AS red,
    COUNT(CASE WHEN(color = 'blue') THEN 1 END) AS blue,
    COUNT(CASE WHEN(color = 'green') THEN 1 END) AS green
  FROM [dbo].[color]

转载于:https://my.oschina.net/u/1246591/blog/266548