34,838
社区成员




select name,sum(case when type='01' then 1 else 0 end) type1,sum(case when type='02' then 1 else 0 end) type2
from 表
where date between '2009-9-2' and '2009-9-3'
group by name
select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)
from TB group by name
select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)
from TB group by name
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-11 20:19:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)
insert [tb]
select 1,'aaa','01','2009-9-2' union all
select 2,'bbb','01','2009-9-3' union all
select 3,'aaa','01','2009-9-3' union all
select 4,'aaa','02','2009-9-3' union all
select 5,'aaa','02','2009-9-3'
--------------开始查询--------------------------
select name,
type1=sum(case type when '01' then 1 else 0 end),
type2=sum(case type when '02' then 1 else 0 end)
from
tb
group by
[name]
order by
[name]
----------------结果----------------------------
/* name type1 type2
---- ----------- -----------
aaa 2 2
bbb 1 0
(2 行受影响)
*/
declare @tb table (id int ,name nvarchar(10),type int,date datetime)
insert into @tb select 1,'aaa','01','2009-9-2'
union all select 2,'bbb','01','2009-9-3'
union all select 3,'aaa','01','2009-9-3'
union all select 4,'aaa','02','2009-9-3'
union all select 5,'aaa','02','2009-9-3'
select name ,count1=sum(case when type=1 then 1 else 0 end),
count2=sum(case when type=2 then 1 else 0 end)
from @tb group by name
name count1 count2
---------- ----------- -----------
aaa 2 2
bbb 1 0
(2 行受影响)
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)
insert [TB]
select 1,'aaa','01','2009-9-2' union all
select 2,'bbb','01','2009-9-3' union all
select 3,'aaa','01','2009-9-3' union all
select 4,'aaa','02','2009-9-3' union all
select 5,'aaa','02','2009-9-3'
select name,COUNT(distinct(date)) as type1,
sum(case type when '02' then 1 else 0 end) as type2
from tb a
where date between '2009-9-1' and '2009-9-30'
group by name
/*
name type1 type2
aaa 2 2
bbb 1 0
*/
怎么都没有加日期select name,
type1=sum(case type when '01' then 1 else 0 end),
type2=sum(case type when '02' then 1 else 0 end)
from tb
group by name
order by name
--> 测试数据:[TB]
--> 我的淘宝:http://shop36766744.taobao.com/
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)
insert [TB]
select 1,'aaa','01','2009-9-2' union all
select 2,'bbb','01','2009-9-3' union all
select 3,'aaa','01','2009-9-3' union all
select 4,'aaa','02','2009-9-3' union all
select 5,'aaa','02','2009-9-3'
select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)
from TB group by name
/*
name
---- ----------- -----------
aaa 2 2
bbb 1 0
(2 行受影响)
*/
drop table [TB]
select name,count(distinct(type1)) as type1,count(distinct(type2)) as type2
from tb group by name