请教一个SQL语句

[leon51]

类似下图的两个表，要求查出Cust A的所有Prod_ID及其对应的颜色为红色、蓝色等,
注意不能用DECODE和if-then-else,因为颜色太多…请教应该如何写呢？

[卖水果的net]

你要的结果，也画一个表出来；

[leon51]

多谢版主，将两个表更新下并附上要求的结果

[js14982]

我没看错需求的话，就是最简单的两表关联？

select a.prod_id,b.color
from prod a,color b
where a.prod_color=b.color_code
  and a.customer='Cust A';

[xijie00]

引用 2 楼 leon51 的回复:

多谢版主，将两个表更新下并附上要求的结果

select a.prod_id, b.color from prod a, color b
where a.customer =  'Cust A'
and a.prod_color = b.color_code
order by a.prod_id

[超萌D小拖鞋]

WITH 
Prod AS 
(
SELECT 'P1' Prod_ID, 'Cust A' Customer, '100' Prod_Color FROM dual
UNION ALL
SELECT 'P2' Prod_ID, 'Cust B' Customer, '103' Prod_Color FROM dual
UNION ALL
SELECT 'P3' Prod_ID, 'Cust C' Customer, '102' Prod_Color FROM dual
UNION ALL
SELECT 'P4' Prod_ID, 'Cust C' Customer, '102' Prod_Color FROM dual
UNION ALL
SELECT 'P5' Prod_ID, 'Cust A' Customer, '101' Prod_Color FROM dual
UNION ALL
SELECT 'P6' Prod_ID, 'Cust A' Customer, '105' Prod_Color FROM dual
),
Color AS
(
SELECT '100' Color_Code, 'Green' Color FROM dual
UNION ALL
SELECT '101' Color_Code, 'Yellow' Color FROM dual
UNION ALL
SELECT '102' Color_Code, 'White' Color FROM dual
UNION ALL
SELECT '103' Color_Code, 'Black' Color FROM dual
UNION ALL
SELECT '104' Color_Code, 'Blue' Color FROM dual
UNION ALL
SELECT '105' Color_Code, 'Red' Color FROM dual
)
SELECT A.prod_id, B.color FROM prod A, color B
WHERE A.prod_color = B.color_code
AND A.customer='Cust A'
ORDER BY A.prod_id

[leon51]

我将原始的需求简单化了，3楼和4楼是我要的结果。5楼的太深奥了，不过应不可行，记录太多怎么办