前不久参加了一个面试,因为笔试SQL不过关导致刷了下来。遇到这样一个问题,感觉很简单但是没想到方法,大家帮忙看看,应该是与group by有关。
rq shengfu
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
rq 胜 负
2005-05-09 2 2
2005-05-10 1 2
------最佳解决方案--------------------
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','负'