SQL 需求问题有大侠能帮解决吗

显示全部楼层 · 2009-10-14 18:49:45

A
B
结果集：
ID
月份
数量
id
月份
数量
月份
1
1
10
1
1
10
1
1
2
20
1
2
20
2
1
3
0
2
3
20
3
1
4
0
2
4
12
4
1
5
0

5
1
6
0

6
1
7
0

7
1
8
0

8
1
9
0

9
1
10
0

10
1
11
0

11
1
12
0

12
2
1
0

2
2
20

2
3
0

2
4
12

2
5
0

2
6
0

2
7
0

2
8
0

2
9
0

2
10
0

2
11
0

2
12
0
补差行的问题不足12个月的补成12各月

千问 · 2009-10-14 18:49:45

cross join

千问 · 2009-10-14 18:49:45

select kk.a,kk.n,a.C froma,
(select a.a,tt.n from (select distinct a from a)a,b tt) kk
where a.b(+)=kk.n and a.a(+)=kk.a
order by kk.a,kk.n

千问 · 2009-10-14 18:49:45

标注一下，以后有用。

千问 · 2009-10-14 18:49:45

http://www.itpub.net/viewthread. ... p%3Bfilter%3Ddigest
参考下这个

SELECT tt.ID, mi.MON, nvl(tt.VAL,0) FROM TEST_TAB tt
partition by (tt.id)
right join mon_idx mi
on tt.mon = mi.mon
[ 本帖最后由 gyhgood 于 2011-6-10 23:17 编辑 ]

千问 · 2009-10-14 18:49:45

SQL> with a as
2 (select level 月份 from dual connect by level <= 12),
3b as
4 (select 1 id, 1 月份, 10 数量 from dual
5union
6select 1 id, 2 月份, 20 数量 from dual
7union
8select 2 id, 3 月份, 20 数量 from dual
9union
10select 2 id, 4 月份, 12 数量 from dual)
11select b.id, a.月份, nvl(b.数量, 0)
12from a
13left join b
14partition by (b.id)
15on a.月份 = b.月份
16/
1
1
10
1
2
20
1
3
0
1
4
0
1
5
0
1
6
0
1
7
0
1
8
0
1
9
0
1 10
0
1 11
0
1 12
0
2
1
0
2
2
0
2
3
20
2
4
12
2
5
0
2
6
0
2
7
0
2
8
0
2
9
0
2 10
0
2 11
0
2 12
0

24 rows selected

SQL 需求问题 有大侠能帮解决吗

SQL 需求问题有大侠能帮解决吗