日期:2014-05-17 浏览次数:20437 次
select rq,sum(case when shengfu='胜' then 1 else 0 end ) '胜',sum(case when shengfu='负' then 1 else 0 end )'负'
from tb
group by rq
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
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] PIVOT(COUNT([shengfu]) FOR [shengfu] IN([胜],[负]) ) pvt
----------------结果----------------------------
/*
rq 胜 负
2005-05-09 00:00:00.000 2 2
2005-05-10 00:00:00.000 1 2
*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
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','负'