关于对字段不同类型求和的问题

ivws_19 2009-09-11 06:05:42
有一张表,类似如下:
id name type date
1 aaa 01 2009-9-2
2 bbb 01 2009-9-3
3 aaa 01 2009-9-3
4 aaa 02 2009-9-3
5 aaa 02 2009-9-3

我如何根据日期查出:
name count(type1) count(type2)
aaa 2 2
bbb 1 0

自己写的语句比较繁琐,求比较简洁的写法,多谢~!
...全文
227 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
qiqi860819 2009-09-11
  • 打赏
  • 举报
回复
想问下sum(1)或sum(0),是什么意思?是计算的列索引么?不明白,还请问大家下
cxmcxm 2009-09-11
  • 打赏
  • 举报
回复
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
xuejiecn 2009-09-11
  • 打赏
  • 举报
回复

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

华夏小卒 2009-09-11
  • 打赏
  • 举报
回复

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

--小F-- 2009-09-11
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行受影响)

*/
ChinaJiaBing 2009-09-11
  • 打赏
  • 举报
回复


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 行受影响)

soft_wsx 2009-09-11
  • 打赏
  • 举报
回复
     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
*/
怎么都没有加日期
sdhdy 2009-09-11
  • 打赏
  • 举报
回复
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
SQL77 2009-09-11
  • 打赏
  • 举报
回复
CASE WHEN
jiangshun 2009-09-11
  • 打赏
  • 举报
回复


--> 测试数据:[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]


soft_wsx 2009-09-11
  • 打赏
  • 举报
回复
select name,count(distinct(type1)) as type1,count(distinct(type2)) as type2
from tb group by name

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧