表RECORD_INFO
|-------|-----------------------|
|NAME | DATE |
|-------|-----------------------|
|b | 2008-06-01 04:01:01 |
|a | 2008-06-01 08:01:01 |
|c | 2008-06-01 08:01:01 |
|b | 2008-06-01 14:01:01 |
|a | 2008-06-01 21:01:01 |
|b | 2008-06-10 02:01:01 |
|a | 2008-06-10 12:01:01 |
|a | 2008-06-14 10:01:01 |
|a | 2008-06-14 05:01:01 |
|a | 2008-06-14 11:01:01 |
|b | 2008-06-14 12:01:01 |
|c | 2008-06-14 12:01:01 |
|b | 2008-06-14 19:01:01 |
|b | 2008-06-14 17:01:01 |
|c | 2008-06-14 12:01:01 |
|-------|-----------------------|表NAME_INFO
|-------|---------|
|NAME | FEE |
|-------|---------|
|a | 8 |
|b | 21 |
|c | 12 |
|-------|---------|
要求写一个SQL语句，从上面两个表中得出如下结果（上述两个表根据NAME字段进行关联）
|-----------|---------|------|
|DATE | 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 |
|-----------|---------|------|谁来作答？本题纯属技术讨论，顾没有给分，见谅！
表RECORD_INFO
------------------------------
NAME | DATE
------------------------------
b | 2008-06-01 04:01:01
a | 2008-06-01 08:01:01
c | 2008-06-01 08:01:01
b | 2008-06-01 14:01:01
a | 2008-06-01 21:01:01
b | 2008-06-10 02:01:01
a | 2008-06-10 12:01:01
a | 2008-06-14 10:01:01
a | 2008-06-14 05:01:01
a | 2008-06-14 11:01:01
b | 2008-06-14 12:01:01
c | 2008-06-14 12:01:01
b | 2008-06-14 19:01:01
b | 2008-06-14 17:01:01
c | 2008-06-14 12:01:01
------------------------------表NAME_INFO
----------------
NAME | FEE
----------------
a | 8
b | 21
c | 12
----------------
要求写一个SQL语句，从上面两个表中得出如下结果（上述两个表根据NAME字段进行关联）
--------------------------
DATE | 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
--------------------------
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');这样子基本上可以得出了....不过还有一点点问题...就是2008-06-10时显示不出c的值...
4楼写的没什么错，可以将
WHERE T1.NAME = T2.NAME
改写成
WHERE T2.NAME = T1.NAME(+)
这样c为0的值也会出现的
(
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
总计 21013 rows selectedSQL>
SELECT *
FROM (SELECT DISTINCT TRUNC(V_DATE) V_DATE
FROM RECORD_INFO) A,
(SELECT *
FROM NAME_INFO) B
ORDER BY TRUNC(V_DATE),
NAME
这个产生笛卡尔积(当然这样做是相当于产生了一个日期,FEE的参照表),但是如果数据量很大时就会出问题了,有可能会把TEMP空间撑爆,
有没有什么更好的方法???