A資料表內容如下:
id or_no color
p1 s0 紅
p2 s0 綠
p3 s2 藍
p4 s2 橘
p5 s0 黑
B資料表如下:
id color typ
p1 紅 a
p5 黑 b
k1 白 c
k2 紫 d請問我今天要查詢A資料表中的or_no如果找不到s9就找s0,如果他與B資料表有重覆就顯示重覆
這是我最後要呈現的資料
id or_no color abc
p1 s0 紅 重覆
p5 s0 黑 重覆
p2 s0 綠select a.*, DECODE(a.id,b.id,'重覆') abc from a, b
where (a.or_no = 's0' or a.or_no = 's9')
請問我該如何修改這段SQL(他出現好多資料唷)
id or_no color
p1 s0 紅
p2 s0 綠
p3 s2 藍
p4 s2 橘
p5 s0 黑
B資料表如下:
id color typ
p1 紅 a
p5 黑 b
k1 白 c
k2 紫 d請問我今天要查詢A資料表中的or_no如果找不到s9就找s0,如果他與B資料表有重覆就顯示重覆
這是我最後要呈現的資料
id or_no color abc
p1 s0 紅 重覆
p5 s0 黑 重覆
p2 s0 綠select a.*, DECODE(a.id,b.id,'重覆') abc from a, b
where (a.or_no = 's0' or a.or_no = 's9')
請問我該如何修改這段SQL(他出現好多資料唷)
where a.or_no = b.or_no
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (id varchar(2),or_no varchar(2),color varchar(2))
insert into #ta
select 'p1','s0','紅' union all
select 'p2','s0','綠' union all
select 'p3','s2','藍' union all
select 'p4','s2','橘' union all
select 'p5','s0','黑'
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id varchar(2),color varchar(2),typ varchar(1))
insert into #tb
select 'p1','紅','a' union all
select 'p5','黑','b' union all
select 'k1','白','c' union all
select 'k2','紫','d'select a.id,a.or_no,a.color,
case when exists(select 1 from #tb where a.id=id and a.color=color) then '重复' else '' end
from #ta a
where or_no in ('s9','s0' )id or_no color
---- ----- ----- ----
p1 s0 紅 重复
p2 s0 綠
p5 s0 黑 重复(3 行受影响)
------是这个意思不?
SQL>
SQL> with a as (
2 select 'p1' id, 's0' or_no, '紅' color from dual union all
3 select 'p2' id, 's0' or_no, '綠' color from dual union all
4 select 'p3' id, 's2' or_no, '藍' color from dual union all
5 select 'p4' id, 's2' or_no, '橘' color from dual union all
6 select 'p5' id, 's0' or_no, '黑' color from dual
7 ),
8 B as(
9 select 'p1' id, '紅' color, 'a' type from dual union all
10 select 'p5' id, '黑' color, 'b' type from dual union all
11 select 'k1' id, '白' color, 'c' type from dual union all
12 select 'k2' id, '紫' color, 'd' type from dual)
13 select a.*,decode(a.id,(select id from B where a.id= b.id),'重复',null) from a where or_no = 's0'
14 ;ID OR_NO COLOR DECODE(A.ID,(SELECTIDFROMBWHER
-- ----- ----- ------------------------------
p1 s0 紅 重复
p2 s0 綠
p5 s0 黑 重复SQL>
------修改下。。有点问题。。
SQL>
SQL> with a as (
2 select 'p1' id, 's0' or_no, '紅' color from dual union all
3 select 'p2' id, 's0' or_no, '綠' color from dual union all
4 select 'p3' id, 's2' or_no, '藍' color from dual union all
5 select 'p4' id, 's2' or_no, '橘' color from dual union all
6 select 'p5' id, 's0' or_no, '黑' color from dual
7 ),
8 B as(
9 select 'p1' id, '紅' color, 'a' type from dual union all
10 select 'p5' id, '黑' color, 'b' type from dual union all
11 select 'k1' id, '白' color, 'c' type from dual union all
12 select 'k2' id, '紫' color, 'd' type from dual)
13 select a.*,decode(a.id,(select id from B where a.id= b.id),'重复',null) from a
14 where or_no = (case when or_no='s9' then 's9' else 's0' end)
15 ;ID OR_NO COLOR DECODE(A.ID,(SELECTIDFROMBWHER
-- ----- ----- ------------------------------
p1 s0 紅 重复
p2 s0 綠
p5 s0 黑 重复SQL>
WITH a AS(
SELECT 'p1'id, 's0'or_no, '红'color FROM dual UNION ALL
SELECT 'p2', 's0', '绿' FROM dual UNION ALL
SELECT 'p3', 's2', '黄' FROM dual UNION ALL
SELECT 'p4', 's2', '橘' FROM dual UNION ALL
SELECT 'p5', 's0', '黑' FROM dual
),
b AS(
SELECT 'p1'id, '红'color, 'a' typ FROM dual UNION ALL
SELECT 'p5', '黑', 'b' FROM dual UNION ALL
SELECT 'k1', '白', 'c' FROM dual UNION ALL
SELECT 'k2', '紫', 'd' FROM dual
)
SELECT a.*,
CASE WHEN exists(SELECT 1 FROM b WHERE a.id=b.id AND a.color=b.color)
THEN '重复'
ELSE NULL
END abc
from a where (a.or_no = 's0' or a.or_no = 's9');
ID OR_NO COLOR ABC
---------------------------
p1 s0 红 重复
p2 s0 绿
p5 s0 黑 重复
SELECT 'p1'id, 's0'or_no, '红'color FROM dual UNION ALL
SELECT 'p2', 's0', '绿' FROM dual UNION ALL
SELECT 'p3', 's2', '黄' FROM dual UNION ALL
SELECT 'p4', 's2', '橘' FROM dual UNION ALL
SELECT 'p5', 's0', '黑' FROM dual
),
b AS(
SELECT 'p1'id, '红'color, 'a' typ FROM dual UNION ALL
SELECT 'p5', '黑', 'b' FROM dual UNION ALL
SELECT 'k1', '白', 'c' FROM dual UNION ALL
SELECT 'k2', '紫', 'd' FROM dual
)
select a.*,decode(sign((select count(*) from b where a.id=b.id)),1,'重复')
from a
where a.or_no = 's0' or a.or_no = 's9'
[code=SQL]
SQL>
SQL> WITH a AS(
2 SELECT 'p1'id, 's0'or_no, '红'color FROM dual UNION ALL
3 SELECT 'p2', 's0', '绿' FROM dual UNION ALL
4 SELECT 'p3', 's2', '黄' FROM dual UNION ALL
5 SELECT 'p4', 's2', '橘' FROM dual UNION ALL
6 SELECT 'p5', 's0', '黑' FROM dual
7 ),
8 b AS(
9 SELECT 'p1'id, '红'color, 'a' typ FROM dual UNION ALL
10 SELECT 'p5', '黑', 'b' FROM dual UNION ALL
11 SELECT 'k1', '白', 'c' FROM dual UNION ALL
12 SELECT 'k2', '紫', 'd' FROM dual
13 )
14 select a.*,decode(sign((select count(*) from b where a.id=b.id)),1,'重复')
15 from a
16 where a.or_no = 's0' or a.or_no = 's9'
17 /
ID OR_NO COLOR DECODE(SIGN((SELECTCOUNT(*)FRO
-- ----- ----- ------------------------------
p1 s0 红 重复
p2 s0 绿
p5 s0 黑 重复
[/code]