sql 语句如何写
0
[已解决问题]
解决于 2015-07-10 14:05
今天想了下，还是没有把写来，还请高手们帮我看下。
拼写的表如下：
select 1 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 2 as 序号 ,'2010-2-3' as 日期,'否' as 状
union all
select 3 as 序号 ,'2010-2-4' as 日期,'是' as 状
union all
select 4 as 序号 ,'2010-2-4' as 日期,'否' as 状
union all
select 5 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 6 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 7 as 序号 ,'2010-2-4' as 日期,'是' as 状
最佳答案
0
select [Time] as 日期,
SUM(CASE WHEN [状态]='是' THEN 1 ELSE 0 END) as 是,
SUM(CASE WHEN [状态]='否' THEN 1 ELSE 0 END) as 否
from [Table]
group by [Time]
这样查一下就出来了，用不着写那么多。
奖励园豆：5
其他回答(3)
0
我只是知道大概的思路，在结果中得到的数据都是分组的情况，所以在查询语句中有ground by，你可以试试。
0
WITH CTE AS ( select 1 as 序号 ,'2010-2-3' as 日期,'是' as 状 union all select 2 as 序号 ,'2010-2-3' as 日期,'否' as 状 union all select 3 as 序号 ,'2010-2-4' as 日期,'是' as 状 union all select 4 as 序号 ,'2010-2-4' as 日期,'否' as 状 union all select 5 as 序号 ,'2010-2-3' as 日期,'是' as 状 union all select 6 as 序号 ,'2010-2-3' as 日期,'是' as 状 union all select 7 as 序号 ,'2010-2-4' as 日期,'是' as 状 ) SELECT 日期, SUM(是) 是, SUM(否) 否 FROM ( SELECT 日期, CASE WHEN 状='是' THEN 1 ELSE 0 END AS 是, CASE WHEN 状='是' THEN 0 ELSE 1 END AS 否 FROM CTE )A GROUP BY 日期
0
WITH CTE AS (
select 1 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 2 as 序号 ,'2010-2-3' as 日期,'否' as 状
union all
select 3 as 序号 ,'2010-2-4' as 日期,'是' as 状
union all
select 4 as 序号 ,'2010-2-4' as 日期,'否' as 状
union all
select 5 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 6 as 序号 ,'2010-2-3' as 日期,'是' as 状
union all
select 7 as 序号 ,'2010-2-4' as 日期,'是' as 状
)
SELECT 日期,
COUNT(CASE WHEN 状='是' THEN 1 END) AS 是,
COUNT(CASE WHEN 状='否' THEN 1 END) AS 否
FROM CTE GROUP BY 日期
