SQL 按天分组查询

有两张表 tab1 和 tab2
它们分别有一个日期字段，记录着本条数据插入时间，格式是 yyyy-MM-dd hh24:mi:ss
要统计的结果集就是每天每张表有多少条数据。

比如需要查询2012年11月份，那么查询的结果集就是30行，分别是2012-11-01到2012-11-30每天的行数。
如果某个日期没有数据，那么显示为0。
最前面的一列要显示每一天的日期，格式：yyyy-MM-dd。

注意：tab2 中要查询多种不同的条件，每一种条件放在结果集的一列中。
比如tab2中有一列col1，那么结果集中就会显示，每一天tab2.col1 = 0的有多少条、每一天tab2.col1=1的有多少条，以此类推。

比较着急，在线等，麻烦各位帮忙看看。

8 个解决方案

#1

来点数据和你的预想结果。不太明白你的描述

#2

tab1
col1Date              其它列
2012-11-01 18:54:29    aaa
2012-11-01 17:12:30    bbb
2012-11-01 05:34:58    sa;dkf;
2012-11-02 07:15:01    ;lkwekre
2012-11-04 12:36:59    429234k;sfd
2012-11-04 15:06:37    lskrfw

tab2
col1Date              status     其它列
2012-11-01 12:15:15    1         kalsjd
2012-11-01 13:17:49    1         qrewuo
2012-11-01 11:13:58    0         wer;few
2012-11-02 09:18:32    0         qwreu
2012-11-04 08:57:02    1         owiejfwe
2012-11-04 06:35:27    1         weiie

就是类似这样的表。

结果集
tab1.Date   tab1.col1     tab2.col1(status=0)   tab2.col2(status=1)   tab2.col3(其它查询条件)
2012-11-01      3           2                      4                      5

其它查询条件可以不考虑，我可以举一反三。

#3

 select t.date, t.c, t1.c1, t1.c3
  from (select trunc(date) date, count(*) c from tab1 group by trunc(date)) t
  left join (select trunc(date) date,
                    sum(decode(status, 0, 1, 0)) c1,
                    sum(decode(status, 0, 2, 0)) c3
               from tab2
              group by trunc(date)) t1 on t.date = t1.date

#4

select *
  from (SELECT TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                       'YYYY-MM-DD') AS tab1Date,
               count(1) as tab1col1
          FROM TEST1
         group by TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                          'YYYY-MM-DD')
         order by TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                          'YYYY-MM-DD'))
  left join (select *
               from (select status from test2) pivot(count(status) for status in ('0' as tab2col1, '1' as tab2col2))) on 1 = 1

#5

引用 3 楼 wanglipo 的回复:

select t.date, t.c, t1.c1, t1.c3
  from (select trunc(date) date, count(*) c from tab1 group by trunc(date)) t
  left join (select trunc(date) date,
                    sum(decode(status, 0, 1, 0)……

高手哇，表示十分敬佩和感谢！
我对查询做了些小修改，这是我修改后的查询

 SELECT t.inDate1, t.c, t1.c1, t1.c3, c4, c5
  FROM (SELECT TRUNC(inDate1) inDate1, COUNT(*) c FROM tab1 GROUP BY TRUNC(inDate1)) t
  LEFT JOIN (SELECT TRUNC(inDate2) inDate2,

--这两个地方有一点没弄明白，为什么把为0的情况分别变为1和2呢？所以我修改成了下面的形式，如果有错误，还请不吝指正。
                    --sum(decode(status, 0, 1, 0)) c1,
                    --sum(decode(status, 0, 2, 0)) c3

                    SUM(DECODE(status, 0, 1, 0)) c1,
                    SUM(DECODE(status, 1, 1, 0)) c3,
                    SUM(DECODE(其它条件字段, NULL, 1, 0, 1, 0)) c4,
                    SUM(DECODE(其它条件字段, 1, 1, 0)) c5
               FROM tab2
              GROUP BY TRUNC(inDate2)) t1 ON t.inDate1 = t1.inDate2
                             WHERE to_char(t.inDate1, 'yyyy-mm') = '2012-11'
                             ORDER BY t.inDate1 ASC;

为了区别tab1和tab2的日期字段的名字，tab1的日期字段名字是inDate1；tab2的日期字段名字是inDate2。

最后，再次表示感谢！

#6

引用 4 楼 restbely 的回复:

select *
  from (SELECT TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                       'YYYY-MM-DD') AS tab1Date,
               count(1) as tab1col1
          FROM TEST1
……

4楼这个没测试，刚刚一直在测试三楼的SQL，没有刷新页面，刚刚刷新了一下才看到。
虽然没有用上，不过也得给个辛苦分，等有时间了一定测试下这个SQL。
同样在此表示感谢。

#7

对了，忘记说一点，对于没有数据的日期并不能显示为0，这个看来只能从程序的手段来解决了。

#8

引用 7 楼 bdy1234567 的回复:

对了，忘记说一点，对于没有数据的日期并不能显示为0，这个看来只能从程序的手段来解决了。

WITH TEST1 AS (
SELECT '2012-11-01 18:54:29' AS col1Date FROM DUAL
UNION ALL
SELECT '2012-11-01 17:12:30' AS col1Date FROM DUAL
UNION ALL
SELECT '2012-11-01 05:34:58' AS col1Date FROM DUAL
UNION ALL
SELECT '2012-11-02 07:15:01' AS col1Date FROM DUAL
UNION ALL
SELECT '2012-11-04 12:36:59' AS col1Date FROM DUAL
UNION ALL
SELECT '2012-11-04 15:06:37' AS col1Date FROM DUAL
),
TEST2 AS (
SELECT '2012-11-01 12:15:15' AS col1Date,'1' AS status FROM DUAL
UNION ALL
SELECT '2012-11-01 13:17:49' AS col1Date,'1' AS status FROM DUAL
UNION ALL
SELECT '2012-11-01 11:13:58' AS col1Date,'0' AS status FROM DUAL
UNION ALL
SELECT '2012-11-02 09:18:32' AS col1Date,'0' AS status FROM DUAL
UNION ALL
SELECT '2012-11-04 08:57:02' AS col1Date,'1' AS status FROM DUAL
UNION ALL
SELECT '2012-11-04 06:35:27' AS col1Date,'1' AS status FROM DUAL
)
SELECT DD,NVL(tab1col1,0) AS tab1col1,tab2col1,tab2col2 FROM (
SELECT TRUNC(SYSDATE,'MM')-1 +ROWNUM  AS DD  FROM DUAL CONNECT BY ROWNUM<=SUBSTR(LAST_DAY(TRUNC(SYSDATE)),7,2)) LEFT JOIN 
(SELECT TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                       'YYYY-MM-DD') AS tab1Date,
               count(1) as tab1col1
          FROM TEST1
         group by TO_CHAR(TO_DATE(col1Date, 'YYYY-MM-DD HH24:MI:SS'),
                          'YYYY-MM-DD')) ON DD =tab1Date
left join (select *
               from (select status from test2) pivot(count(status) for status in ('0' as tab2col1, '1' as tab2col2))) on 1 = 1
               ORDER BY DD

也可以有。