我有一表 aa，代表客戶的基本編碼信息，其中id代表標(biāo)識(shí)，類別代表客戶的類別，當(dāng)類別為1的時(shí)候代表是個(gè)人客戶，為2時(shí)代表單位客戶
ID 類別
1 1
2 2
3 1
3 2
另一表bb，該表為個(gè)人客戶表
id 名稱
1 張三
2 李四
3 王五
還有一表cc 該表為單位客戶表
id 名稱
1 工行
2 農(nóng)行
3 建行
我想通過(guò)一個(gè)語(yǔ)句查詢出所有客戶的id和名稱，即我期望的結(jié)果集是
id 名稱
1 張三
2 農(nóng)行
3 王五
3 建行
哪位高手指點(diǎn)一下這個(gè)sql怎樣寫，用decode可以么
先謝了
john_student 回復(fù)于：2004-01-10 14:05:00
難題啊，我也想知道，我的是informix，
實(shí)際中確實(shí)有這種情況，sql怎么寫呢？
feijin 回復(fù)于：2004-01-10 14:35:38
如果和類別沒有關(guān)系可以用union
select * from a
union
select * from b
toworm 回復(fù)于：2004-01-10 15:40:39
我要的就是根據(jù)類別列不同的取值關(guān)聯(lián)不同的表啊
CCBZZP 回復(fù)于：2004-01-10 16:24:35
建議用
SELECT ...
UNION
SELECT ...
UNION
...
rollingpig 回復(fù)于：2004-01-10 16:41:46
create view customer_info_2
as (
select *,1 from bb
union
select *,2 from cc)
understand?
toworm 回復(fù)于：2004-01-10 18:10:28
union誰(shuí)不會(huì)啊，那樣效率不是很低么
我實(shí)際應(yīng)用的表很大的啊
decode不行么，oracle上有沒有case的用法啊
seraphim 回復(fù)于：2004-01-11 01:48:42
9i以后oracle就支持case了
huanggzcn 回復(fù)于：2004-01-11 01:54:30
select aa.id,decode(aa.類別,1,bb.名稱,cc.名稱)
from aa,bb,cc
where aa.id=bb.id and aa.id=cc.id
做一個(gè)三表的關(guān)聯(lián)，并把記錄數(shù)最少的表名放在From子句最右邊，
再對(duì)[aa.類別]進(jìn)行判別，如果是[1]就取[bb.名稱]
如果是[2]就取[cc.名稱]
這樣的速度并不慢，肯定比Union快
ohwww 回復(fù)于：2004-01-11 09:21:50
這個(gè)問題挺有意思,學(xué)習(xí)....
toworm 回復(fù)于：2004-01-11 10:20:59
引用：原帖由 "huanggzcn" 發(fā)表：
這樣的速度并不慢，肯定比Union快
這樣肯定是不行的，請(qǐng)參見我下面的例子
[color=red]select * from aa[/color]
ID TYPE
---------- ----------
1 1
2 1
3 2
4 2
4 rows selected
[color=red]select * from bb[/color]
ID NAME
---------- --------------------
1 張三
2 李四
2 rows selected
[color=red]select * from cc[/color]
ID NAME
---------- --------------------
3 工行
4 農(nóng)行
2 rows selected
[color=red]select aa.id,decode(aa.type,1,bb.name,cc.name)
from aa,bb,cc
where aa.id=bb.id and aa.id=cc.id[/color]
ID DECODE(AA.TYPE,1,BB.NAME,CC.NA
---------- ------------------------------
0 rows selected
[color=red]select aa.id,decode(aa.type,1,bb.name,cc.name)
from aa,bb,cc
where aa.id=bb.id or aa.id=cc.id[/color]
ID DECODE(AA.TYPE,1,BB.NAME,CC.NA
---------- ------------------------------
3 工行
3 工行
4 農(nóng)行
4 農(nóng)行
1 張三
1 張三
2 李四
2 李四
8 rows selected
總之不論是用and還是or結(jié)果集的數(shù)量是不符合實(shí)際需求的
[color=red]因?yàn)槲覀儗?shí)際是想要得到
1 張三
2 李四
3 工行
4 農(nóng)行
如果通過(guò)or方式再進(jìn)行distinct估計(jì)比union還要慢，何況例子只是2個(gè)表，如果更多的話，結(jié)果集會(huì)更多 [/color]
john_student 回復(fù)于：2004-01-11 11:11:11
用and條件的改一下：
aa與bb，cc分別為外部連接應(yīng)該就可以的了。
具體語(yǔ)法Oracle的不太懂，好像是:
from aa, bb(+), cc(+) ????????
informix是這樣的（兄弟我只會(huì)informix）：
from aa, outer bb, outer cc
我沒有環(huán)境測(cè)試oracle，哪位測(cè)試過(guò)后，告訴我結(jié)果哦。THANKS
^0^
huanggzcn 回復(fù)于：2004-01-11 14:24:52
我上面的語(yǔ)句沒有考慮在表bb, cc中不存在記錄的情況，只需要使用outer join就可以完成樓主的要求了，具體的sql如下：
select aa.id, decode(aa.type, 1, bb.name, cc.name)
from aa, bb, cc
where aa.id(+) = bb.id
and aa.id(+) = cc.id;
這樣效率就不知道怎么樣了，個(gè)人意見是表連接還是會(huì)比使用Union要快些
小弟在此拋磚引玉了
toworm 回復(fù)于：2004-01-11 14:31:49
引用：原帖由 "huanggzcn" 發(fā)表：
我上面的語(yǔ)句沒有考慮在表bb, cc中不存在記錄的情況，只需要使用outer join就可以完成樓主的要求了，具體的sql如下：
select aa.id, decode(aa.type, 1, bb.name, cc.name)
from aa, bb, cc
where aa.id(+) = b..........
好像不行啊
1 select aa.id, decode(aa.type, 1, bb.name, cc.name)
2 from aa, bb, cc
3 where aa.id (+)= bb.id
4* and aa.id (+)= cc.id
SQL>; /
and aa.id (+)= cc.id
*
[color=red]ERROR Î&ÓÚ&Ú 4 ÐÐ:
ORA-01417: a table may be outer joined to at most one other table [/color]
toworm 回復(fù)于：2004-01-11 14:37:08
上面說(shuō)的可以，是＋號(hào)寫反了
1 select aa.id, decode(aa.type, 1, bb.name, cc.name)
2 from aa, bb, cc
3 where aa.id= bb.id (+)
4* and aa.id = cc.id (+)
即可
huanggzcn 回復(fù)于：2004-01-11 14:39:59
不好意思，是我寫反了，表aa的數(shù)據(jù)最全，搞錯(cuò)了
toworm 回復(fù)于：2004-01-11 14:44:24
我又試了其他幾種情況，好像都沒問題，謝了
上面那個(gè)錯(cuò)誤主要還不是表aa數(shù)據(jù)不全的問題，好像錯(cuò)誤提示是同一個(gè)表只能外連接一個(gè)表，而不能同時(shí)有多個(gè)
huanggzcn 回復(fù)于：2004-01-11 14:59:26
如果假設(shè)表是下面的情況
aa
------
2 aa2
bb
------
1 bb1
2 bb2
cc
------
2 cc2
3 cc3
如果aa和bb外連接的結(jié)果是:
1 bb1
2 aa2 2 bb2
如果aa和cc外連接是
2 aa2 2 cc2
3 cc3
如果是aa.id(+) = bb.id and aa.id(+) = cc.id的話，
同時(shí)把表aa和兩個(gè)表外連接的話我還沒想出來(lái)結(jié)果是怎么樣的
所以我覺得剛才那個(gè)錯(cuò)誤也是這個(gè)道理吧
voi-jia 回復(fù)于：2004-01-12 00:11:00
:em02:
ohwww 回復(fù)于：2004-01-12 11:05:32
不太明白。
不過(guò)我想實(shí)現(xiàn)如下功能：根據(jù)表中某個(gè)字段的值的不同而去關(guān)聯(lián)不同的表
如表a
col1 col2
130 aa
131 bb
133 cc
如果col1的值為133則關(guān)聯(lián)表1，為130和131就關(guān)聯(lián)表2，該怎么寫呢？？
tinywind 回復(fù)于：2004-01-12 11:22:24
這種寫法會(huì)比union好嘛？希望樓主能貼出些比較結(jié)果。
feijin 回復(fù)于：2004-01-12 13:48:29
呵呵 個(gè)人認(rèn)為表連接效率不如union
看樓主的意思 我的做法出來(lái)結(jié)果有問題？
toworm 回復(fù)于：2004-01-12 15:30:19
我也不知道到底那種好，只是我的理解是如果實(shí)際需求是根據(jù)一個(gè)字段關(guān)聯(lián)多個(gè)表，如果不考慮索引，理論上如果關(guān)聯(lián)n個(gè)表，則用union實(shí)際要對(duì)基表全表掃描n次，而用連接應(yīng)該是一次就夠了。不知哪位大蝦能實(shí)際做一下分析或測(cè)試
huanggzcn 回復(fù)于：2004-01-13 01:17:44
減少訪問數(shù)據(jù)庫(kù)的次數(shù)
當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量, 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫(kù)的次數(shù), 就能實(shí)際上減少ORACLE的工作量.
例如,
以下有三種方法可以檢索出雇員號(hào)等于0342或0291的職員.
方法1 (最低效)
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …, .., .. ;
…..
OPEN C1(291);
FETCH C1 INTO …, .., .. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME, A.SALARY, A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A, EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意:
在SQL*Plus, SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫(kù)訪問的檢索數(shù)據(jù)量, 建議值為200
轉(zhuǎn)自--《ORACLE SQL性能優(yōu)化系列》
toworm 回復(fù)于：2004-01-13 09:24:31
還是不知道最終的分析結(jié)果
wind0299 回復(fù)于：2004-01-13 09:43:44
GOOD
huanggzcn 回復(fù)于：2004-01-13 18:13:47
為測(cè)試效率的情況，我今天特意做了個(gè)實(shí)驗(yàn)
測(cè)試環(huán)境為以下[均無(wú)使用索引，機(jī)器為閑置的測(cè)試機(jī)]
SQL>; desc hzh_aa
Name Null? Type
------------------------------- -------- ----
ID NUMBER
TYPE NUMBER(38 )
SQL>; desc hzh_bb
Name Null? Type
------------------------------- -------- ----
ID NUMBER
LB VARCHAR2(2)
SQL>; desc hzh_cc
Name Null? Type
------------------------------- -------- ----
ID NUMBER
LB VARCHAR2(2)
SQL>;
運(yùn)行以下代碼往表中插內(nèi)容：
declare
i number;
begin
for i in 1..100000 loop
insert into hzh_aa values(i,1);
insert into hzh_aa values(i,2);
end loop;
commit;
end;
/
declare
i number;
begin
for i in 1..100000 loop
insert into hzh_bb values(i,‘OK‘);
insert into hzh_cc values(i,‘NO‘);
end loop;
commit;
end;
/
set timing on --打開timing
select count(*) from (
select aa.id, decode(aa.type, 1, bb.lb, cc.lb)
from hzh_aa aa, hzh_bb bb, hzh_cc cc
where aa.id= bb.id (+)
and aa.id = cc.id (+));
連續(xù)三次執(zhí)行的時(shí)間分別為：[7691] [7681] [7671]
select count(*) from (
select hzh_aa.id,lb
from hzh_aa,hzh_bb
where hzh_aa.id=hzh_bb.id and hzh_aa.type=1
union
select hzh_aa.id,lb
from hzh_aa,hzh_cc
where hzh_aa.id=hzh_cc.id and hzh_aa.type=2);
連續(xù)三次執(zhí)行的時(shí)間分別為：[9193] [9133] [9153]
這是aa表只有20萬(wàn)條記錄時(shí)的差距，在實(shí)際應(yīng)用中的數(shù)據(jù)量是遠(yuǎn)遠(yuǎn)大于這個(gè)的。
toworm 回復(fù)于：2004-01-14 10:38:23
這兩天我總是隱隱覺得有些問題，今天想明白了，只有保證幾個(gè)表之間的數(shù)據(jù)完整性的前提下用decode加外連接的方式才可以等同與union方式，否則它們得到的結(jié)果集是不同的，請(qǐng)看下面的例子
select * from aa
ID TYPE
---------- ----------
1 1
2 1
3 2
4 2
5 1
[color=red] 5 2[/color]
6 2
7 rows selected
select * from bb
ID NAME
---------- --------------------
1 張三
2 李四
5 王五
3 rows selected
select * from cc
ID NAME
---------- --------------------
3 工行
4 農(nóng)行
6 招行
3 rows selected
select aa.id, decode(aa.type, 1, bb.name, cc.name)
from aa, bb, cc
where aa.id= bb.id (+)
and aa.id = cc.id (+)
ID DECODE(AA.TYPE,1,BB.NAME,CC.NA
---------- ------------------------------
1 張三
2 李四
3 工行
4 農(nóng)行
5 王五
[color=red] 5 [/color]
6 招行
7 rows selected
select aa.id,bb.name from aa,bb
where aa.id=bb.id
and aa.type=1
union
select aa.id,cc.name from aa,cc
where aa.id=cc.id
and aa.type=2
ID NAME
---------- --------------------
1 張三
2 李四
5 王五
3 工行
4 農(nóng)行
6 招行
6 rows selected
大家可以看一下紅色的部分，如果在基表中有這條記錄，但在子表中沒有的話，用union方式是不會(huì)選出這條記錄的，而用外連接則選出一條記錄為空的記錄沒，有什么辦法讓外連接方式也不包含這條記錄么
