原始数据表:Date id count
2013-05-23 1 1
2013-05-23 2 3
2013-05-26 1 100
2013-06-01 2 200
2013-06-06 1 20
2013-06-06 2 30期望结果:
2013-05-23 1 1
2013-06-01 1 0
2013-05-26 1 100
2013-06-06 1 20
2013-05-23 2 3
2013-05-26 2 0
2013-06-01 2 200
2013-06-06 2 30如果当天有一个id的有数据,其他的id要补齐数据,count为0. 然后结果按id分组排序,每组id中按日期排序
2013-05-23 1 1
2013-05-23 2 3
2013-05-26 1 100
2013-06-01 2 200
2013-06-06 1 20
2013-06-06 2 30期望结果:
2013-05-23 1 1
2013-06-01 1 0
2013-05-26 1 100
2013-06-06 1 20
2013-05-23 2 3
2013-05-26 2 0
2013-06-01 2 200
2013-06-06 2 30如果当天有一个id的有数据,其他的id要补齐数据,count为0. 然后结果按id分组排序,每组id中按日期排序
2013-05-23 1 1
2013-05-23 2 3
2013-05-26 1 100
2013-06-01 2 200
2013-06-06 1 20
2013-06-06 2 30期望结果:
2013-05-23 1 1
2013-06-01 1 0
2013-05-26 1 100
2013-06-06 1 20
2013-05-23 2 3
2013-05-26 2 0
2013-06-01 2 200
2013-06-06 2 30如果当天有一个id的有数据,其他的id要补齐数据,count为0. 然后结果按id分组排序,每组id中按日期排序
CREATE TABLE test(dat VARCHAR2(8),id VARCHAR2(10),cnt VARCHAR2(10));INSERT INTO test VALUES('20130523','1','1');
INSERT INTO test VALUES('20130523','2','3');
INSERT INTO test VALUES('20130526','1','100');
INSERT INTO test VALUES('20130601','2','200');
INSERT INTO test VALUES('20130606','1','20');
INSERT INTO test VALUES('20130606','2','30');--先產生dat和id的各种组合
WITH T1 AS (SELECT DISTINCT dat FROM test),T2 AS (SELECT DISTINCT id FROM test)--再和原Table做左外连接
SELECT T3.DAT,T3.ID,Nvl(Sum(T4.CNT),0)cnt FROM (SELECT * FROM T1,T2)T3,TEST T4 WHERE T3.DAT=T4.DAT(+) AND T3.ID=T4.ID(+) GROUP BY T3.DAT,T3.ID ORDER BY T3.ID;--查詢結果DAT ID CNT
20130523 1 1
20130526 1 100
20130601 1 0
20130606 1 20
20130523 2 3
20130526 2 0
20130601 2 200
20130606 2 30
with t as
(
select '2013-05-23' "date", 1 id, 1 "count" from dual
union all
select '2013-05-23' "date", 2 id, 3 "count" from dual
union all
select '2013-05-26' "date", 1 id, 100 "count" from dual
union all
select '2013-06-01' "date", 2 id, 200 "count" from dual
union all
select '2013-06-06' "date", 1 id, 20 "count" from dual
union all
select '2013-06-06' "date", 2 id, 30 "count" from dual
)
select t."date",
(case when (t2.cid=1 and t.id = 1) then 2 else t.id end) id,
(case when (t2.cid=1 and t.id = 1) then 0 else t."count" end) "count"
from
(
select "date",count(id) cid from t t1 group by "date"
) t2,t
where t."date"=t2."date" and cid=1
union all
select t."date",
t.id,
t."count"
from t 要开会了, 不看起来嵌套有点多,也没有分组排序,不过LZ 可以加点代码实现,应该不难。
总体思路是先自己构造没有的数据,然后union原先的数据。
with t as
(
select 668001 oldsubsid , 668009 newsubsid from dual
union all
select 668009 oldsubsid , 668005 newsubsid from dual
union all
select 668002 oldsubsid , 668010 newsubsid from dual
union all
select 668010 oldsubsid , 668019 newsubsid from dual
union all
select 668019 oldsubsid , 668044 newsubsid from dual
union all
select 668003 oldsubsid , 668017 newsubsid from dual
)select t.oldsubsid, t from t SYS_CONNECT_BY_PATH(DEPNAME, '/')
with t as
(
select '2013-05-23' "date", 1 id, 1 "count" from dual
union all
select '2013-05-23' "date", 2 id, 3 "count" from dual
union all
select '2013-05-26' "date", 1 id, 100 "count" from dual
union all
select '2013-06-01' "date", 2 id, 200 "count" from dual
union all
select '2013-06-06' "date", 1 id, 20 "count" from dual
union all
select '2013-06-06' "date", 2 id, 30 "count" from dual
union all
select '2013-06-07' "date", 2 id, 40 "count" from dual
)
select t."date",
(case when t.id = 1 then 2 else 1 end) id,
0 "count"
from
(
select "date",count(id) cid from t t1 group by "date"
) t2,t
where t."date"=t2."date" and cid=1
union all
select t."date",
t.id,
t."count"
from t
刚才要开会,打的有点急, 有点问题,修正一下。
这个不要 我测其他东西的,不小心粘 上来了。下班喽。