高分求救,一个高难度的SQL语句??

田九 软件开发  2006-11-19 05:19:42
一个高难度的SQL语句,不知道能不能实现。
如果表A有符合条件的,就取A的记录,如果没有就取表B的记录。
表A和表B结构是一样的
...全文
435 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
xueru9999 2006-11-29
我同意love_2008(love2008) 的写法
回复
小李木耳 2006-11-29
select * from a1 where 条件
union
select * from a2 where 条件 and (select count(*) from a1 where 条件)=0
回复
wangzk0206 2006-11-28
select * from a where 条件
union
select * from b where 条件 and not exists (select 1 from a where 条件)
回复
Eric_1999 2006-11-28
select * from a where exist (select 1 from a where 你的条件)
union
select * from b where exist (select 1 from b where 你的条件)
and not exist (select 1 from a where 你的条件)
回复
tongyu10068 2006-11-20
declare
v_temp number(10);
v_temp2 varchar(30);
begin
v_temp2 :=&输入条件;
select count(*) into v_temp from a where 列名 =v_temp2;
if (v_temp)!=0 then
select * from a where 列名 =v_temp2;
else
select * from b where 列名 =v_temp2;
end if;
end;
回复
kingkingkingking 2006-11-20
如果结构相同,数据量不是很大
用以下语句
select * from a1 where 条件
union
select * from a2 where 条件
两个语句的条件相同即可
回复
hevin 2006-11-19
写了一个例子,你可以参考一下:

SQL> select * from a;

C1 C2
------- --------------------
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
105 David

6 rows selected

SQL> select * from b;

C1 C2
------- --------------------
103 Alexander
104 Bruce
105 David
106 Valli
107 Diana
108 Nancy
109 Daniel

7 rows selected

SQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 > 101
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 > 101 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 > 101 AND rownum <=1);

C1 C2
------- --------------------
102 Lex
103 Alexander
104 Bruce
105 David

SQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 > 106
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 > 106 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 > 106 AND rownum <=1);

C1 C2
------- --------------------
107 Diana
108 Nancy
109 Daniel

SQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 101
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =101 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 101 AND rownum <=1);

C1 C2
------- --------------------
101 Neena

SQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 104
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =104 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 104 AND rownum <=1);

C1 C2
------- --------------------
104 Bruce

SQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 108
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =108 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 108 AND rownum <=1);

C1 C2
------- --------------------
108 Nancy

SQL>
回复
田九 2006-11-19
因为有可能,两个表都满足的情况呀。
回复
田九 2006-11-19
这样写不行,如果该条件,都满足呢,不就都取出来了。
回复
火龙岛主 2006-11-19
select * from a where exist (select 1 from a where 你的条件)
union
select * from b where exist (select 1 from b where 你的条件)
回复
相关推荐
发帖
基础和管理
创建于2007-09-28

1.7w+

社区成员

Oracle 基础和管理
申请成为版主
帖子事件
创建了帖子
2006-11-19 05:19
社区公告
暂无公告