sql-server - 将颜色名称转换为 RGB

Question

我有一个记录表，每个记录表存储一个颜色名称。例如:

Product    |  Colour
-------------------
Product A  |  Blue
Product B  |  Black

我添加了 3 个新列:R、G 和 B。如何使用单个 SQL 查询将颜色转换为 RGB 值？

Answer 1

;with Colours(Name, R, G, B) as
(
  select 'White',   255, 255, 255 union all
  select 'Silver',  192, 192, 192 union all
  select 'Gray',    128, 128, 128 union all
  select 'Black',   0  , 0  , 0   union all
  select 'Red',     255, 0  , 0   union all
  select 'Maroon',  128, 0  , 0   union all
  select 'Yellow',  255, 255, 0   union all
  select 'Olive',   128, 128, 0   union all
  select 'Lime',    0  , 255, 0   union all
  select 'Green',   0  , 128, 0   union all
  select 'Aqua',    0  , 255, 255 union all
  select 'Teal',    0  , 128, 128 union all
  select 'Blue',    0  , 0  , 255 union all
  select 'Navy',    0  , 0  , 128 union all
  select 'Fuchsia', 255, 0  , 255 union all
  select 'Purple',  128, 0  , 128
)
update P set
  R = C.R,
  G = C.G,
  B = C.B
from products as P
  inner join Colours as C
    on P.Colour = C.Name