当产品为Red、Green或Blue时，如何返回Y/N

Question

我有一个SQL表，看起来像这样(t1)

________________________________________________
|Id | ProductName | ColorID | ProductDescription|
|1  | x           | 7       | x                 |
-------------------------------------------------

另一个表包含了Colors (t2)

_________________
| Id | Color    | 
| 1  | Red      |
| 2  | Orange   |
| 3  | Yellow   |
| 4  | Green    |
| 5  | Blue     |
| .. | ...      |
-----------------

我想做的是有一个查询，如果产品是红色，绿色或蓝色，根据它的colorID

返回是或否

____________________________
|Id | ProductName | Is_RGB |
| 1 | x           | N      |
----------------------------

这就是我最初所做的，它成功了:

select t1.id as 'Id', 
t1.ProductName as 'ProductName',
case when t1.ColorID in ('1', '4', '5') then 'Y' else 'N' end as 'Is_RGB'
from t1

但是有一种方法，我可以在查询中使用颜色名称，而不是colorId?比如在('红色'，'绿色'，'蓝色')等情况下....请帮助。蒂娅!

Answer 1

如果你愿意加入，你可以做。例如:

select t1.id as 'Id', 
t1.ProductName as 'ProductName',
case when t2.Color in ('Red', 'Green', 'Blue') then 'Y' else 'N' end as 'Is_RGB'
from t1
inner join t2
on ts.ColorID = t2.id

Answer 2

select 
p.id,
p.name,
case 
when c.name in('Red','Green','Blue') then 'Y' 
else 'N' 
end as color_flag
from product p
inner join color c on c.id=p.color_id;