mssql如何把两个查询合并成一个
0
悬赏园豆：30
[已解决问题]
解决于 2012-11-27 15:54
sql语句1：select count(Azcount) as 新 from StatisticsInfon
where addtime between '2012-11-14 00:00:00' and '2012-11-14 23:59:59'
sql语句2：select count(Azcount) as 旧 from StatisticsInfon
where addtime between '2012-11-13 00:00:00' and '2012-11-13 23:59:59'
用union合并之后，只能显示一列。
如何变成这种一个结果集的效果？
新 旧
12 12
最佳答案
1
select sum(case when addtime between '2012-11-13 00:00:00' and '2012-11-13 23:59:59' then 1 else 0 end) as 旧,sum(case when addtime between '2012-11-14 00:00:00' and '2012-11-14 23:59:59' then 1 else 0 end) as 新
from StatisticsInfon
收获园豆：30
表A ID Time count
数据 1 2012-11-13 01:05:00 1
2 2012-11-14 03:01:00 1
返回结果
Time(小时) NewCount(14号数据) OldCount(13号数据)
0-1 0(Null) 0(Null)
1-2 0(Null) 1
2-3 0(Null) 0(Null)
3-4 1 0(Null)
........
23-24 0(null) 0(null)
那这样要如何做呢？
@假扮天使:
先创建一个表H(hour int) ，数据0-23
select a.hour,b.newcount,c.oldcount from
H a left join
(select datepart(hh,time) as h,sum(isnull(count,0)) as newcount from tableA where time between '2012-11-14 0:00:00' and '2012-11-14 23:59:59' group by datepart(hh,time)) b on a.hour=b.h
left join (select datepart(hh,time) as h,sum(isnull(count,0)) as oldcount from tableA where time between '2012-11-13 0:00:00' and '2012-11-13 23:59:59' group by datepart(hh,time)) c on a.hour=c.h
其他回答(2)
1
select count(Azcount) as 新, (
select count(Azcount) as 旧 from StatisticsInfon
where addtime between '2012-11-13 00:00:00' and '2012-11-13 23:59:59'
)from StatisticsInfon
where addtime between '2012-11-14 00:00:00' and '2012-11-14 23:59:59'
0
SELECT *
FROM ( SELECT COUNT(*) 新
FROM StatisticsInfon
WHERE xxxx
) a ,
( SELECT COUNT(*) 旧
FROM StatisticsInfon
WHERE xxxxxx
) b