有一张表
id 日期 数量
1 2010-02 10
2 2010-02 10
3 2010-04 20
4 2010-06 70
按日期分组求和 从2010-01到2010-07想得到以下形式 如何写sql oracle下
日期 数量
2010-01 0
2010-02 20
2010-03 0
2010-04 20
2010-05 0
2010-06 70
2010-07 0
id 日期 数量
1 2010-02 10
2 2010-02 10
3 2010-04 20
4 2010-06 70
按日期分组求和 从2010-01到2010-07想得到以下形式 如何写sql oracle下
日期 数量
2010-01 0
2010-02 20
2010-03 0
2010-04 20
2010-05 0
2010-06 70
2010-07 0
select '2010-'||lpad(level,2,0) a
from dual connect by level <= 12
)select t1.a,sum(t2.数量)
from table_name t2
where t2.日期(+) = t1.a
group by t1.a;
(SELECT '2011-02' month_id,10 num FROM dual
UNION ALL SELECT '2011-02' ,10 num FROM dual
UNION ALL SELECT '2011-04' ,20 num FROM dual
UNION ALL SELECT '2011-06' ,70 num FROM dual)
SELECT n.month_id,SUM(nvl(t.num,0)) num FROM t,
(SELECT to_char(add_months(to_date('2011-01','yyyy-mm'),ROWNUM-1),'yyyy-mm') month_id
FROM dual CONNECT BY ROWNUM<=7) n
WHERE n.month_id = t.month_id(+)
GROUP BY n.month_id
ORDER BY n.month_id;
SELECT 1 AS ID, '2010-02' AS MONTH, 10 AS qty FROM dual UNION
SELECT 2, '2010-02', 10 FROM dual UNION
SELECT 3, '2010-04', 20 FROM dual UNION
SELECT 4, '2010-06', 70 FROM dual
)
SELECT t2.MONTH, DECODE(t1.month, NULL, 0, t1.qty)
FROM (SELECT monTh, sum(qTy) AS qty
FROM T
GROUP BY MONTH) t1,
(SELECT to_char(add_months(to_date('2010-01', 'yyyy-mm'),ROWNUM - 1), 'yyyy-mm') AS MONTH
FROM dual
CONNECT BY ROWNUM <= MONTHS_BETWEEN(to_date('2010-07', 'yyyy-mm'), to_date('2010-01' , 'yyyy-mm')) + 1
) t2
WHERE t1.MONTH(+) = t2.MONTH
ORDER BY t2.month
SELECT OP_TIME ,SUM(CNT) FROM TABLE_NAME WHERE OP_TIME BETWEEN '2010-01' AND '2010-07' GROUP BY OP_TIME ;