---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-04-09 22:12:32 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) -- Jun 17 2011 00:57:23 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([Name] varchar(1),[Type] int) insert [huang] select 'A',0 union all select 'B',1 union all select 'C',0 union all select 'D',1 union all select 'A',0 union all select 'A',1 union all select 'B',0 --------------开始查询--------------------------select [name],Type0 =SUM(CASE WHEN [Type]=0 THEN 1 ELSE 0 END ), Type1 =SUM(CASE WHEN [Type]=1 THEN 1 ELSE 0 END ) from [huang] GROUP BY [name] ----------------结果---------------------------- /* name Type0 Type1 ---- ----------- ----------- A 2 1 B 1 1 C 1 0 D 0 1(4 行受影响) */
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-09 22:12:32
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Name] varchar(1),[Type] int)
insert [huang]
select 'A',0 union all
select 'B',1 union all
select 'C',0 union all
select 'D',1 union all
select 'A',0 union all
select 'A',1 union all
select 'B',0
--------------开始查询--------------------------select [name],Type0 =SUM(CASE WHEN [Type]=0 THEN 1 ELSE 0 END ),
Type1 =SUM(CASE WHEN [Type]=1 THEN 1 ELSE 0 END )
from [huang]
GROUP BY [name]
----------------结果----------------------------
/*
name Type0 Type1
---- ----------- -----------
A 2 1
B 1 1
C 1 0
D 0 1(4 行受影响)
*/
同意二楼,但是觉得这里sum改为count比较好,看楼主意思应该是求次数的。