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
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
高手哇,表示十分敬佩和感谢! 我对查询做了些小修改,这是我修改后的查询 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。最后,再次表示感谢!
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 也可以有。
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 lskrfwtab2
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其它查询条件可以不考虑,我可以举一反三。
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
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
我对查询做了些小修改,这是我修改后的查询 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。最后,再次表示感谢!
虽然没有用上,不过也得给个辛苦分,等有时间了一定测试下这个SQL。
同样在此表示感谢。
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
也可以有。