前不久参加了一个面试,因为笔试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
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
from tb
group by rq
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','负'
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
/*
rq 胜 负
----------------------- ----------- -----------
2005-05-09 00:00:00.000 2 2
2005-05-10 00:00:00.000 1 2
(2 行受影响)
*/
(
rq date, shengfu varchar(4)
)insert into @t values
('2005-05-09','胜'),
('2005-05-09','胜'),
('2005-05-09','负'),
('2005-05-09','负'),
('2005-05-10','胜'),
('2005-05-10','负'),
('2005-05-10','负')select * from @t
pivot
(
count([shengfu]) for shengfu in (胜,负)
)
as pvt
还真是的,估计刚来我们公司面试过
真是奇怪!我好久没来csdn了,这次莫名其妙过来看,莫名其妙点了这个帖子
select convert(varchar(20),rq,23) as rq,sum(case when shengfu='胜' then 1 else 0 end) as 胜,
sum(case when shengfu='负' then 1 else 0 end)as 负 from tb group by rq order by rq
select rq,
count(case when shengfu='胜' then shengfu end) as '胜',
count(case when shengfu='负' then shengfu end) as '负'
from #test group by rq
面试的.NET软件工程师,现在的基本考题都是数据库,