请问Oracle下多表的分组查询怎么做？？

Question

select a.monitordate, count(a.monitordate)
from ho3gto2gcell a
where a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate order by a.monitordate查询出来是
1 2010/3/25 13:15:00 408
2 2010/3/25 13:20:00 413
3 2010/3/25 13:25:00 409
4 2010/3/25 13:30:00 395
5 2010/3/25 13:35:00 445
6 2010/3/25 13:40:00 425
7 2010/3/25 13:45:00 417
8 2010/3/25 13:50:00 427
9 2010/3/25 13:55:00 453
10 2010/3/25 14:00:00 489
11 2010/3/25 14:05:00 421
12 2010/3/25 14:10:00 403
13 2010/3/25 14:15:00 390
14 2010/3/25 14:20:00 431
select a.monitordate, count(a.monitordate) as HOSUCCESSAIUCSCELL
from HOSUCCESSAIUCSCELL a 
where a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate 
order by a.monitordate查询出来是
1 2010/3/25 13:15:00 30184
现在我想把上面的合并在一起查询怎么弄？？
也就是
         日期                   ho3gto2gcell   HOSUCCESSAIUCSCELL                  
1 2010/3/25 13:15:00 408          30184
2 2010/3/25 13:20:00 413          0
3 2010/3/25 13:25:00 409          0
4 2010/3/25 13:30:00 395          0
5 2010/3/25 13:35:00 445          0
6 2010/3/25 13:40:00 425          0
7 2010/3/25 13:45:00 417          0
8 2010/3/25 13:50:00 427          0
9 2010/3/25 13:55:00 453          0
10 2010/3/25 14:00:00 489          0
11 2010/3/25 14:05:00 421          0
12 2010/3/25 14:10:00 403          0
13 2010/3/25 14:15:00 390          0
14 2010/3/25 14:20:00 431          0

Answer 1

select a.monitordate, count(a.monitordate)ho3gto2gcell, count(b.monitordate) HOSUCCESSAIUCSCELL   
from ho3gto2gcell a，HOSUCCESSAIUCSCELL b
where a.monitordate = b.monitordate
and a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate 
order by a.monitordate

Answer 2

应该这样
select a.monitordate, count(a.monitordate)ho3gto2gcell, nvl(count(b.monitordate),0)HOSUCCESSAIUCSCELL   
from ho3gto2gcell a left join HOSUCCESSAIUCSCELL b on a.monitordate = b.monitordate
where a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate  
order by a.monitordate

Answer 3

2楼你好，这2张表的monitordate是一样的，但是没什么关联

Answer 4

select a.monitordate,ho3gto2gcell,nvl(HOSUCCESSAIUCSCELL,0) HOSUCCESSAIUCSCELL
from (select a.monitordate, count(a.monitordate) ho3gto2gcell
from ho3gto2gcell a
where a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate ) a 
left join (select a.monitordate, count(a.monitordate) as HOSUCCESSAIUCSCELL
from HOSUCCESSAIUCSCELL a 
where a.monitordate between to_date('2010-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2010-03-29 23:59:00','YYYY-MM-DD HH24:MI:SS')
group by a.monitordate 
) b on a.monitordate=b.monitordate
order by a.monitordate

Answer 5

那个如果我还有类似的表的话是不是在后面继续Left join

Answer 6

SELECT a.monitordate, a.cnt, nvl(HOSUCCESSAIUCSCELL,0) HOSUCCESSAIUCSCELL
  FROM (SELECT rownum rn, monitordate, cnt
          FROM (SELECT a.monitordate, COUNT(a.monitordate) AS cnt
                  FROM ho3gto2gcell a
                 WHERE a.monitordate BETWEEN to_date('2010-03-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2010-03-29 23:59:00', 'YYYY-MM-DD HH24:MI:SS')
                 GROUP BY a.monitordate
                 ORDER BY a.monitordate)) c,
       (SELECT rownum rn, monitordate, HOSUCCESSAIUCSCELL
          FROM (SELECT a.monitordate, COUNT(a.monitordate) AS HOSUCCESSAIUCSCELL
                  FROM HOSUCCESSAIUCSCELL a
                 WHERE a.monitordate BETWEEN to_date('2010-03-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                       to_date('2010-03-29 23:59:00', 'YYYY-MM-DD HH24:MI:SS')
                 GROUP BY a.monitordate
                 ORDER BY a.monitordate)) d
 WHERE c.rn = d.rn(+);

Answer 7

12楼的也可以的晕分散的太快了不好意思，a.monitordate a.cnt