表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果,该如何写SQL语句
胜 负
2005-05-09 2 2
2005-05-10 1 2
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果,该如何写SQL语句
胜 负
2005-05-09 2 2
2005-05-10 1 2
,sum(case when col='胜' then 1 else 0 end) as [胜]
,sum(case when col='负' then 1 else 0 end) as 负
from tb
group by date
insert tb select '2005-05-09','胜'
insert tb select '2005-05-09','胜'
insert tb select '2005-05-09','负'
insert tb select '2005-05-09','负'
insert tb select '2005-05-10','胜'
insert tb select '2005-05-10','负'
insert tb select '2005-05-10','负'
select date
,sum(case when col='胜' then 1 else 0 end) as [胜]
,sum(case when col='负' then 1 else 0 end) as 负
from tb
group by datedate 胜 负
---------- ----------- -----------
2005-05-09 2 2
2005-05-10 1 2(2 行受影响)
create_date varchar(10),
tag nvarchar(10)
);Insert #t (create_date, tag)
Values
('2005-05-09','胜'),
('2005-05-09','胜'),
('2005-05-09','负'),
('2005-05-09','负'),
('2005-05-10','胜'),
('2005-05-10','负'),
('2005-05-10','负')
Goselect * from #t;select create_date, 胜, 负
from
(select create_date, tag
from #t) a
pivot(
count(tag)
for tag in ([胜],[负])
)b
中间,就会显示
insert into lint1 select '2005-05-09', '胜'
insert into lint1 select '2005-05-09', '负'
insert into lint1 select '2005-05-09', '负'
insert into lint1 select '2005-05-09', '胜'
insert into lint1 select '2005-05-10', '胜'
insert into lint1 select '2005-05-10', '负'
insert into lint1 select '2005-05-10', '负'
goselect dt,胜利=count(case when id='胜' then 1 end),失败=count(case when id='负' then 1 end) from lint1 group by dt
go
drop table lint1
sum(case col2 when 胜 then 1 else 0 end) as 胜,
sum(case col2 when 负 then 1 else 0 end) as 负
from ta
group by 日期
from tb
group by date