|
我有一表 aa，代表客户的基本编码信息，其中id代表标识，类别代表客户的类别，当类别为1的时候代表是个人客户，为2时代表单位客户
ID 类别
1 1
2 2
3 1
3 2
另一表bb，该表为个人客户表
id 名称
1 张三
2 李四
3 王五
还有一表cc 该表为单位客户表
id 名称
1 工行
2 农行
3 建行
我想通过一个语句查询出所有客户的id和名称，即我期望的结果集是
id 名称
1 张三
2 农行
3 王五
3 建行
哪位高手指点一下这个sql怎样写，用decode可以么
先谢了
john_student 回复于：2004-01-10 14:05:00
难题啊，我也想知道，我的是informix，
实际中确实有这种情况，sql怎么写呢？
feijin 回复于：2004-01-10 14:35:38
如果和类别没有关系可以用union
select * from a
union
select * from b
toworm 回复于：2004-01-10 15:40:39
我要的就是根据类别列不同的取值关联不同的表啊
CCBZZP 回复于：2004-01-10 16:24:35
建議用
SELECT ...
UNION
SELECT ...
UNION
...
rollingpig 回复于：2004-01-10 16:41:46
create view customer_info_2
as (
select *,1 from bb
union
select *,2 from cc)
understand?
toworm 回复于：2004-01-10 18:10:28
union谁不会啊，那样效率不是很低么
我实际应用的表很大的啊
decode不行么，oracle上有没有case的用法啊
seraphim 回复于：2004-01-11 01:48:42
9i以后oracle就支持case了
huanggzcn 回复于：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
做一个三表的关联，并把记录数最少的表名放在From子句最右边，
再对[aa.类别]进行判别，如果是[1]就取[bb.名称]
如果是[2]就取[cc.名称]
这样的速度并不慢，肯定比Union快
ohwww 回复于：2004-01-11 09:21:50
这个问题挺有意思,学习....
toworm 回复于：2004-01-11 10:20:59
引用：原帖由 "huanggzcn" 发表：
这样的速度并不慢，肯定比Union快
这样肯定是不行的，请参见我下面的例子
[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 农行
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 农行
4 农行
1 张三
1 张三
2 李四
2 李四
8 rows selected
总之不论是用and还是or结果集的数量是不符合实际需求的
[color=red]因为我们实际是想要得到
1 张三
2 李四
3 工行
4 农行
如果通过or方式再进行distinct估计比union还要慢，何况例子只是2个表，如果更多的话，结果集会更多 [/color]
john_student 回复于：2004-01-11 11:11:11
用and条件的改一下：
aa与bb，cc分别为外部连接应该就可以的了。
具体语法Oracle的不太懂，好像是:
from aa, bb(+), cc(+) ????????
informix是这样的（兄弟我只会informix）：
from aa, outer bb, outer cc
我没有环境测试oracle，哪位测试过后，告诉我结果哦。THANKS
^0^
huanggzcn 回复于：2004-01-11 14:24:52
我上面的语句没有考虑在表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;
这样效率就不知道怎么样了，个人意见是表连接还是会比使用Union要快些
小弟在此抛砖引玉了
toworm 回复于：2004-01-11 14:31:49
引用：原帖由 "huanggzcn" 发表：
我上面的语句没有考虑在表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 回复于：2004-01-11 14:37:08
上面说的可以，是＋号写反了
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 回复于：2004-01-11 14:39:59
不好意思，是我写反了，表aa的数据最全，搞错了
toworm 回复于：2004-01-11 14:44:24
我又试了其他几种情况，好像都没问题，谢了
上面那个错误主要还不是表aa数据不全的问题，好像错误提示是同一个表只能外连接一个表，而不能同时有多个
huanggzcn 回复于：2004-01-11 14:59:26
如果假设表是下面的情况
aa
------
2 aa2
bb
------
1 bb1
2 bb2
cc
------
2 cc2
3 cc3
如果aa和bb外连接的结果是:
1 bb1
2 aa2 2 bb2
如果aa和cc外连接是
2 aa2 2 cc2
3 cc3
如果是aa.id(+) = bb.id and aa.id(+) = cc.id的话，
同时把表aa和两个表外连接的话我还没想出来结果是怎么样的
所以我觉得刚才那个错误也是这个道理吧
voi-jia 回复于：2004-01-12 00:11:00
:em02:
ohwww 回复于：2004-01-12 11:05:32
不太明白。
不过我想实现如下功能：根据表中某个字段的值的不同而去关联不同的表
如表a
col1 col2
130 aa
131 bb
133 cc
如果col1的值为133则关联表1，为130和131就关联表2，该怎么写呢？？
tinywind 回复于：2004-01-12 11:22:24
这种写法会比union好嘛？希望楼主能贴出些比较结果。
feijin 回复于：2004-01-12 13:48:29
呵呵 个人认为表连接效率不如union
看楼主的意思 我的做法出来结果有问题？
toworm 回复于：2004-01-12 15:30:19
我也不知道到底那种好，只是我的理解是如果实际需求是根据一个字段关联多个表，如果不考虑索引，理论上如果关联n个表，则用union实际要对基表全表扫描n次，而用连接应该是一次就够了。不知哪位大虾能实际做一下分析或测试
huanggzcn 回复于：2004-01-13 01:17:44
减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少ORACLE的工作量.
例如,
以下有三种方法可以检索出雇员号等于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中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量, 建议值为200
转自--《ORACLE SQL性能优化系列》
toworm 回复于：2004-01-13 09:24:31
还是不知道最终的分析结果
wind0299 回复于：2004-01-13 09:43:44
GOOD
huanggzcn 回复于：2004-01-13 18:13:47
为测试效率的情况，我今天特意做了个实验
测试环境为以下[均无使用索引，机器为闲置的测试机]
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>;
运行以下代码往表中插内容：
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 (+));
连续三次执行的时间分别为：[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);
连续三次执行的时间分别为：[9193] [9133] [9153]
这是aa表只有20万条记录时的差距，在实际应用中的数据量是远远大于这个的。
toworm 回复于：2004-01-14 10:38:23
这两天我总是隐隐觉得有些问题，今天想明白了，只有保证几个表之间的数据完整性的前提下用decode加外连接的方式才可以等同与union方式，否则它们得到的结果集是不同的，请看下面的例子
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 农行
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 农行
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 农行
6 招行
6 rows selected
大家可以看一下红色的部分，如果在基表中有这条记录，但在子表中没有的话，用union方式是不会选出这条记录的，而用外连接则选出一条记录为空的记录没，有什么办法让外连接方式也不包含这条记录么
| |
|
|
