表(table1)内容:结构为 rq(日期) shengfu(胜负)
rq shengfu
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
rq shengfu
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
if object_id('[TB]') is not null drop table [TB]
create table [TB]([rq] datetime,[shengfu] varchar(2))
insert [TB]
select '2005-05-09','胜' union all
select '2005-05-09','胜' union all
select '2005-05-09','负' union all
select '2005-05-09','负' union all
select '2005-05-10','胜' union all
select '2005-05-10','负' union all
select '2005-05-10','负'select * from [TB]SELECT [date]=CONVERT(VARCHAR(10),rq,120),[胜]=sum(CASE WHEN shengfu = '胜' THEN 1 ELSE 0 end),
[负]=sum(CASE WHEN shengfu = '负' THEN 1 ELSE 0 end)
FROM dbo.TB
GROUP BY CONVERT(VARCHAR(10),rq,120)/*
date 胜 负
2005-05-09 2 2
2005-05-10 1 2*/
select rq,sum(case when shengfu = '胜' then 1 else 0 end) as 胜,sum(case when shengfu = '负' then 1 else 0 end) as 负
from table1
group by rq
if object_id('tempdb.dbo.#') is not null drop table #
create table #(rq datetime, shengfu varchar(8))
insert into #
select '2005-05-09', '胜' union all
select '2005-05-09', '胜' union all
select '2005-05-09', '负' union all
select '2005-05-09', '负' union all
select '2005-05-10', '胜' union all
select '2005-05-10', '负' union all
select '2005-05-10', '负'select rq,
胜=sum(case shengfu when '胜' then 1 else 0 end),
负=sum(case shengfu when '负' then 1 else 0 end)
from # group by rq/*
rq 胜 负
----------------------- ----------- -----------
2005-05-09 00:00:00.000 2 2
2005-05-10 00:00:00.000 1 2
*/
DROP TABLE [tb]
CREATE TABLE [tb]
(
[rq] VARCHAR(10) NULL ,
[shengfu] varchar(10) NULL
)
GO--插入测试数据
INSERT INTO [tb] ([rq],[shengfu])
SELECT '2005-05-09','胜' UNION ALL
SELECT '2005-05-09','胜' UNION ALL
SELECT '2005-05-09','负' UNION ALL
SELECT '2005-05-09','负' UNION ALL
SELECT '2005-05-10','胜' UNION ALL
SELECT '2005-05-10','负' UNION ALL
SELECT '2005-05-10','负'
GOSELECT rq,
SUM(CASE shengfu WHEN '胜' THEN 1 ELSE 0 END ) 胜 ,
SUM(CASE shengfu WHEN '负' THEN 1 ELSE 0 END ) 负
FROM tb GROUP BY rq/*
rq 胜 负
---------- ----------- -----------
2005-05-09 2 2
2005-05-10 1 2
*/