3,490
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE record_info(NAME,dates) AS
(
select 'b' name, to_date('2008-06-01 04:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-01 08:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'c' name, to_date('2008-06-01 08:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'b' name, to_date('2008-06-01 14:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-01 21:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'b' name, to_date('2008-06-10 02:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-10 12:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-14 10:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-14 05:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'a' name, to_date('2008-06-14 11:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'b' name, to_date('2008-06-14 12:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'c' name, to_date('2008-06-14 12:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'b' name, to_date('2008-06-14 19:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'b' name, to_date('2008-06-14 17:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual UNION ALL
select 'c' name, to_date('2008-06-14 12:01:01','yyyy-mm-dd hh24:mi:ss') dates from dual);
CREATE TABLE name_info(NAME,fee) AS
(
SELECT 'a' NAME, 8 fee FROM dual UNION
SELECT 'b' NAME, 21 fee FROM dual UNION
SELECT 'c' NAME, 12 fee FROM dual)
SQL> SELECT A.DATES,DECODE(GROUPING_id(a.DATES,a.NAME),1,'合计',3,'总计',A.NAME) name,sum(NVL(A.FEE*B.RN,0)) FEE FROM
2 (SELECT * FROM (select DISTINCT TRUNC(DATES) DATES FROM record_info) A,
3 (SELECT * FROM NAME_INFO) B
4 ORDER BY TRUNC(DATES),NAME) A,
5 (SELECT TRUNC(DATES) DATES,NAME,COUNT(*) RN FROM RECORD_INFO GROUP BY TRUNC(DATES),NAME ) B
6 WHERE A.DATES=B.DATES(+) AND A.NAME=B.NAME(+)
7 GROUP BY ROLLUP(a.DATES,a.NAME)
8 /
DATES NAME FEE
----------- ---- ----------
2008/06/01 a 16
2008/06/01 b 42
2008/06/01 c 12
2008/06/01 合计 70
2008/06/10 a 8
2008/06/10 b 21
2008/06/10 c 0
2008/06/10 合计 29
2008/06/14 a 24
2008/06/14 b 63
2008/06/14 c 24
2008/06/14 合计 111
总计 210
13 rows selected
SQL>
SELECT TO_CHAR(T1.V_DATE, 'yyyymmdd'),
CASE
WHEN T1.NAME IS NULL THEN
'合计'
ELSE
T1.NAME
END,
SUM(T2.FEE)
FROM RECORD_INFO T1,
NAME_INFO T2
WHERE T1.NAME = T2.NAME
GROUP BY ROLLUP(TO_CHAR(T1.V_DATE, 'yyyymmdd'), T1.NAME)
ORDER BY TO_CHAR(T1.V_DATE, 'yyyymmdd');