n*6
2 楼
我的土办法,不知是否可行。
1。把id1, id2连起来,变成id3。
2。
SELECT A.ID3, B.ColumnB1, B.ColumnB2, C.ColumnC1, C.ColumnC2
FROM A LEFT JOIN B ON A.ID3 = B.ID3
RIGHT JOIN C ON A.ID3 = B.ID3
细节和优化方案正在思索中。
1。把id1, id2连起来,变成id3。
2。
SELECT A.ID3, B.ColumnB1, B.ColumnB2, C.ColumnC1, C.ColumnC2
FROM A LEFT JOIN B ON A.ID3 = B.ID3
RIGHT JOIN C ON A.ID3 = B.ID3
细节和优化方案正在思索中。
B*g
3 楼
SELECT a.id1, a.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
FROM A a, B b
WHERE a.id1 = b.id2
AND a.id2 = b.id2
UNION ALL
SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
FROM A a, C c
WHERE a.id1 = c.id2
AND a.id2 = c.id2
有包子吗?
【在 a******8 的大作中提到】![](/moin_static193/solenoid/img/up.png)
: 现有3张表A, B, C
: A (Id1, Id2) with data
: 1 1
: 1 2
: 1 3
: 1 4
FROM A a, B b
WHERE a.id1 = b.id2
AND a.id2 = b.id2
UNION ALL
SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
FROM A a, C c
WHERE a.id1 = c.id2
AND a.id2 = c.id2
有包子吗?
【在 a******8 的大作中提到】
![](/moin_static193/solenoid/img/up.png)
: 现有3张表A, B, C
: A (Id1, Id2) with data
: 1 1
: 1 2
: 1 3
: 1 4
a*8
5 楼
十分感谢,包子送上!
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png)
: SELECT a.id1, a.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
: FROM A a, B b
: WHERE a.id1 = b.id2
: AND a.id2 = b.id2
: UNION ALL
: SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
: FROM A a, C c
: WHERE a.id1 = c.id2
: AND a.id2 = c.id2
: 有包子吗?
【在 B*****g 的大作中提到】
![](/moin_static193/solenoid/img/up.png)
: SELECT a.id1, a.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
: FROM A a, B b
: WHERE a.id1 = b.id2
: AND a.id2 = b.id2
: UNION ALL
: SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
: FROM A a, C c
: WHERE a.id1 = c.id2
: AND a.id2 = c.id2
: 有包子吗?
z*3
6 楼
大牛们指教一下,没有调试过的说。。
create table d as select a.id1,a.id2,b.colb1,b.colb2,c.colc1,c.colc2 from a,b
,c where 1=0;
insert into d as select * from b;
insert into d as select * from c;
create table d as select a.id1,a.id2,b.colb1,b.colb2,c.colc1,c.colc2 from a,b
,c where 1=0;
insert into d as select * from b;
insert into d as select * from c;
z*3
8 楼
vivian chow真的是不老玉女啊。。
a*8
9 楼
- 表A的index是扫描一遍还是两遍?估计优化后是一遍。
- 如果要返回两个结果集(去掉UNION ALL一行),应该会扫描两次。有办法只扫描表A一
次吗?
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png)
: SELECT a.id1, a.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
: FROM A a, B b
: WHERE a.id1 = b.id2
: AND a.id2 = b.id2
: UNION ALL
: SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
: FROM A a, C c
: WHERE a.id1 = c.id2
: AND a.id2 = c.id2
: 有包子吗?
- 如果要返回两个结果集(去掉UNION ALL一行),应该会扫描两次。有办法只扫描表A一
次吗?
【在 B*****g 的大作中提到】
![](/moin_static193/solenoid/img/up.png)
: SELECT a.id1, a.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
: FROM A a, B b
: WHERE a.id1 = b.id2
: AND a.id2 = b.id2
: UNION ALL
: SELECT a.id1, a.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
: FROM A a, C c
: WHERE a.id1 = c.id2
: AND a.id2 = c.id2
: 有包子吗?
B*g
10 楼
SELECT a.id1, a.id2, d.ColumnB1, d.ColumnB2, d.ColumnC1, d.ColumnC2
FROM A a,
(SELECT b.id1, b.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
FROM B b
UNION ALL
SELECT c.id1, c.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
FROM C c
) d
WHERE a.id1 = d.id2
AND a.id2 = d.id2
【在 a******8 的大作中提到】![](/moin_static193/solenoid/img/up.png)
: - 表A的index是扫描一遍还是两遍?估计优化后是一遍。
: - 如果要返回两个结果集(去掉UNION ALL一行),应该会扫描两次。有办法只扫描表A一
: 次吗?
FROM A a,
(SELECT b.id1, b.id2, b.ColumnB1, b.ColumnB2, NULL ColumnC1, NULL ColumnC2
FROM B b
UNION ALL
SELECT c.id1, c.id2, NULL ColumnB1, NULL ColumnB2, ColumnC1, ColumnC2
FROM C c
) d
WHERE a.id1 = d.id2
AND a.id2 = d.id2
【在 a******8 的大作中提到】
![](/moin_static193/solenoid/img/up.png)
: - 表A的index是扫描一遍还是两遍?估计优化后是一遍。
: - 如果要返回两个结果集(去掉UNION ALL一行),应该会扫描两次。有办法只扫描表A一
: 次吗?
d*h
11 楼
select A.id1, A.id2, B.ColumnB1 ColumnB1, B.ColumnB2 ColumnB2, NULL as
ColumnC1, NULL as ColumnC2
from table1 A
right join table2 B
on A.id1=B.id1
and A.id2=B.id2
union all
select A.id1, A.id2, NULL as ColumnB1, NULL as ColumnB2, C.ColumnC1 ColumnC1
, C.ColumnC2 ColumnC2
from table1 A
right join table3 C
on A.id1=C.id1
and A.id2=C.id2;
ColumnC1, NULL as ColumnC2
from table1 A
right join table2 B
on A.id1=B.id1
and A.id2=B.id2
union all
select A.id1, A.id2, NULL as ColumnB1, NULL as ColumnB2, C.ColumnC1 ColumnC1
, C.ColumnC2 ColumnC2
from table1 A
right join table3 C
on A.id1=C.id1
and A.id2=C.id2;
b*e
12 楼
Why you need Table A?
Table B and C is enough.
Table B and C is enough.
a*8
13 楼
It's just an example for simplicity, there are other fields in table A.
d*h
15 楼
select A.id1, A.id2, B.ColumnB1 ColumnB1, B.ColumnB2 ColumnB2, NULL as
ColumnC1, NULL as ColumnC2
from A
right join B
on A.id1=B.id1
and A.id2=B.id2
union all
select A.id1, A.id2, NULL as ColumnB1, NULL as ColumnB2, C.ColumnC1 ColumnC1
, C.ColumnC2 ColumnC2
from A
right join C
on A.id1=C.id1
and A.id2=C.id2;
ColumnC1, NULL as ColumnC2
from A
right join B
on A.id1=B.id1
and A.id2=B.id2
union all
select A.id1, A.id2, NULL as ColumnB1, NULL as ColumnB2, C.ColumnC1 ColumnC1
, C.ColumnC2 ColumnC2
from A
right join C
on A.id1=C.id1
and A.id2=C.id2;
相关阅读
This board seems to be closable[转载] how to set up the classpath?[转载] Whcich DB Software to Install?Comparing different commercial available DBMS?第三个问题can .pc and .c make difference?want help on RDBMSIs that possible for Applet to access the Oracle?how to calculate[转载] LDAP + SQLACT4.0 (Symantec)Another one数据库日志关于数据库有什么可学的呢?最早的debase(拼法正确否)有没有unix下的版本呢?en???数据库版是新开的?SQL question HELPsomething about SQLwhat about oracle,repository&service?help! 怎么insert一个多行的string