现在我有数据库no id date result
1 sh1234 2010-10-9 投诉
2 sh1234 2010-10-9 表扬
3 sh1234 2010-10-10 投诉
4 sh1234 2010-10-10 表扬
5 sh1234 2010-10-10 投诉然后需要生成一下结果 日期 表扬 投诉
2010-10-9 1 1
2010-10-10 1 2
语句该如何写呢??
1 sh1234 2010-10-9 投诉
2 sh1234 2010-10-9 表扬
3 sh1234 2010-10-10 投诉
4 sh1234 2010-10-10 表扬
5 sh1234 2010-10-10 投诉然后需要生成一下结果 日期 表扬 投诉
2010-10-9 1 1
2010-10-10 1 2
语句该如何写呢??
select date,sum(case when result = '投诉' then 1 else 0 end) as 投诉,
sum(case when result='表扬' then 1 else 0 end) as 表扬
from tablename
group by date
[表扬] = count(case when result = '表扬'then 1 else 0 end),
[投诉] =count(case when result = '投诉'then 1 else 0 end)
from TB
group by date
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (no int,id varchar(6),date date,result varchar(4))
insert into tb
select 1, 'sh1234', '2010-10-9' ,'投诉' union all
select 2, 'sh1234', '2010-10-9' ,'表扬' union all
select 3, 'sh1234', '2010-10-10', '投诉' union all
select 4, 'sh1234', '2010-10-10', '表扬' union all
select 5, 'sh1234', '2010-10-10', '投诉'select date 日期 ,表扬 = sum(case result when '表扬' then 1 else 0 end),
投诉 = sum(case result when '投诉' then 1 else 0 end)
from tb
group by date日期 表扬 投诉
2010-10-09 1 1
2010-10-10 1 2
if object_id('tempdb.dbo.#') is not null drop table #
create table #(no int, id varchar(8), date datetime, result varchar(8))
insert into #
select 1, 'sh1234', '2010-10-9', '投诉' union all
select 2, 'sh1234', '2010-10-9', '表扬' union all
select 3, 'sh1234', '2010-10-10', '投诉' union all
select 4, 'sh1234', '2010-10-10', '表扬' union all
select 5, 'sh1234', '2010-10-10', '投诉'select date,
表扬 = count(case result when '表扬' then 1 end),
投诉 = count(case result when '投诉' then 1 end)
from # group by dateselect date, 表扬, 投诉
from
(select date, result from #) t
pivot
(count(result) for result in (表扬,投诉)) p/*
date 表扬 投诉
----------------------- ----------- -----------
2010-10-09 00:00:00.000 1 1
2010-10-10 00:00:00.000 1 2
*/
if object_id('tablename') >0
drop table tablename
create table tablename
(
[no] int,
id varchar(10),
date datetime,
result varchar(20)
)insert into tablename values(1,'sh1234','2010-10-9',N'投诉')
insert into tablename values(2,'sh1234','2010-10-9',N'表扬')
insert into tablename values(3,'sh1234','2010-10-10',N'投诉')
insert into tablename values(4,'sh1234','2010-10-10',N'表扬')
insert into tablename values(5,'sh1234','2010-10-10',N'投诉')
select date,sum(case when result = '投诉' then 1 else 0 end) as 投诉,
sum(case when result='表扬' then 1 else 0 end) as 表扬
from tablename
group by date
结果date 投诉 表扬
2010-10-09 00:00:00.000 1 1
2010-10-10 00:00:00.000 2 1
[表扬] = sum(case when result = '表扬'then 1 else 0 end),
[投诉] =sum(case when result = '投诉'then 1 else 0 end)
from TB
group by datecount ??? sum!!!!
drop table tablename
create table tablename(no int, id varchar(20), date date,result varchar(20)
)
insert tablename
values
(1,'sh1234','2010-10-9','投诉'),
(2,'sh1234','2010-10-9','表扬'),
(3,'sh1234','2010-10-10','投诉'),
(4,'sh1234','2010-10-10','表扬'),
(5,'sh1234','2010-10-10','投诉')select * from tablename select 日期 ,表扬,投诉
from
(
select id , result, [date] as 日期 from tablename
) p
PIVOT
(
count (id)
FOR result IN
(表扬,投诉)
) AS pvt
ORDER BY pvt.日期;