首页 新闻 搜索 专区 学院

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

假扮天使的主页 假扮天使 | 初学一级 | 园豆:30
提问于:2012-11-14 10:48
< >
分享
最佳答案
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
飞来飞去 | 老鸟四级 |园豆:2057 | 2012-11-14 12:45

表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)

 那这样要如何做呢?

假扮天使 | 园豆:30 (初学一级) | 2012-11-14 14:56

@假扮天使: 

先创建一个表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

飞来飞去 | 园豆:2057 (老鸟四级) | 2012-11-14 16:54
其他回答(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'

哇~怪兽 | 园豆:603 (小虾三级) | 2012-11-14 11:25
0

SELECT  *
FROM    ( SELECT    COUNT(*) 新
          FROM      StatisticsInfon
          WHERE     xxxx
        ) a ,
        ( SELECT    COUNT(*) 旧

          FROM      StatisticsInfon
          WHERE     xxxxxx

        ) b

夏狼哉 | 园豆:833 (小虾三级) | 2012-11-14 11:35
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册