首页 新闻 搜索 专区 学院

SQL2005语句转换成oracle语句 按照某天 按照某月 分段查询数据 希望懂sql和oracle的大神看看 帮忙改一下

0
悬赏园豆:5 [已解决问题] 解决于 2015-11-20 15:30
WITH t1([hour],title) AS (
    SELECT 0,' 0:00:00--- 1:00:00' UNION ALL
    SELECT 1,' 1:00:00--- 2:00:00' UNION ALL
    SELECT 2,' 2:00:00--- 3:00:00' UNION ALL
    SELECT 3,' 3:00:00--- 4:00:00' UNION ALL
    SELECT 4,' 4:00:00--- 5:00:00' UNION ALL
    SELECT 5,' 5:00:00--- 6:00:00' UNION ALL
    SELECT 6,' 6:00:00--- 7:00:00' UNION ALL
    SELECT 7,' 7:00:00--- 8:00:00' UNION ALL
    SELECT 8,' 8:00:00--- 9:00:00' UNION ALL
    SELECT 9,' 9:00:00--- 10:00:00' UNION ALL
    SELECT 10,' 10:00:00--- 11:00:00' UNION ALL
    SELECT 11,' 11:00:00--- 12:00:00' UNION ALL
    SELECT 12,' 12:00:00--- 13:00:00' UNION ALL
    SELECT 13,'13:00:00---14:00:00' UNION ALL
    SELECT 14,'14:00:00---15:00:00' UNION ALL
    SELECT 15,'15:00:00---16:00:00' UNION ALL
    SELECT 16,'16:00:00---17:00:00' UNION ALL
    SELECT 17,'17:00:00---18:00:00' UNION ALL
    SELECT 18,'18:00:00---19:00:00' UNION ALL
    SELECT 19,'19:00:00---20:00:00' UNION ALL
    SELECT 20,'20:00:00---21:00:00' UNION ALL
    SELECT 21,'21:00:00---22:00:00' UNION ALL
    SELECT 22,'22:00:00---23:00:00' UNION ALL
    SELECT 22,'23:00:00---24:00:00' 
)
,t2 AS (
    SELECT DatePart(hour,[Time]) [hour],
           AVG(Avspeed) Avspeed, -- 这些字段该怎么算自己调整
           SUM(Occupancy) Occupancy,
           SUM(Volume) Volume
      FROM dbo.T_RadarStatistics
     WHERE [Time] >= '2015-05-20'
       AND [Time] < '2015-05-21'
  GROUP BY DatePart(hour,[Time])
)
    SELECT t1.title,
           t2.Avspeed,
           t2.Occupancy,
           t2.Volume
      FROM t1
 LEFT JOIN t2
        ON t1.[hour] = t2.[hour]
  ORDER BY t1.[hour]
WITH t1([dd],title) AS (
    SELECT 1,' 1日' UNION ALL
    SELECT 2,' 2日' UNION ALL
    SELECT 3,' 3日' UNION ALL
    SELECT 4,' 4日' UNION ALL
    SELECT 5,' 5日' UNION ALL
    SELECT 6,' 6日' UNION ALL
    SELECT 7,' 7日' UNION ALL
    SELECT 8,' 8日' UNION ALL
    SELECT 9,' 9日' UNION ALL
    SELECT 10,' 10日' UNION ALL
    SELECT 11,' 11日' UNION ALL
    SELECT 12,' 12日' UNION ALL 
    SELECT 13,' 13日' UNION ALL
    SELECT 14,' 14日' UNION ALL
    SELECT 15,' 15日' UNION ALL
    SELECT 16,' 16日' UNION ALL
    SELECT 17,' 17日' UNION ALL
    SELECT 18,' 18日' UNION ALL
    SELECT 19,' 19日' UNION ALL
    SELECT 20,' 20日' UNION ALL
    SELECT 21,' 21日' UNION ALL
    SELECT 22,' 22日' UNION ALL
    SELECT 23,' 23日' UNION ALL
    SELECT 24,' 24日' UNION ALL 
    SELECT 25,' 25日' UNION ALL 
    SELECT 26,' 26日' UNION ALL 
    SELECT 27,' 27日' UNION ALL 
    SELECT 28,' 28日' UNION ALL 
    SELECT 29,' 29日' UNION ALL 
    SELECT 30,' 30日' UNION ALL 
    SELECT 31,' 31日' 
)
,t2 AS (
    SELECT DatePart(dd,[Time]) dd,
           AVG(Avspeed) Avspeed,
           SUM(Occupancy) Occupancy,
           SUM(Volume) Volume
      FROM dbo.T_RadarStatistics
     where Time >= '2015-04-01 00:00:00' and Time <= '2015-04-30 23:59:59'  and  address='192.168.0.81'
  GROUP BY DatePart(dd,[Time])
)
    SELECT t1.title,
           t2.Avspeed,
           t2.Occupancy,
           t2.Volume
      FROM t1
 LEFT JOIN t2
        ON t1.[dd] = t2.[dd]
  ORDER BY t1.[dd]
何小宝的主页 何小宝 | 初学一级 | 园豆:19
提问于:2015-05-23 16:21
< >
分享
最佳答案
0

搞这么多,不就是为了构造两个数据嘛,还那么有规律的。有这个必要么。

收获园豆:5
| 小虾三级 |园豆:770 | 2015-05-25 14:12
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册