将一个值标签的单列与SQL的另一个表中的多列相结合到值的多列

Question

我有两个表：首选项，其中显示了学生的用餐偏好， key ，这是了解每个用餐项目的每个值的关键。

Preferences:

| Student_ID | Student_Name | Meat | Vegetable | Drink | Dessert |
|------------|--------------|------|-----------|-------|---------|
| 1          | Jeff         | 3    | 1         | 4     | 1       |
| 2          | Andrea       | 1    | 1         | 3     | 1       |
| 3          | Allison      | 3    | 2         | 7     | 1       |
| 4          | Randy        | 1    | 1         | 4     | 2       |
| 5          | Carl         | 2    | 5         | 2     | 2       |
| 6          | Bobby        | 1    | 6         | 7     | 2       |
| 7          | Julie        | 3    | 5         | 2     | 1       |
| 8          | Anna         | 1    | 6         | 7     | 2       |
| 9          | Carlos       | 1    | 6         | 2     | 2       |
| 10         | Roger        | 2    | 4         | 2     | 1       |
| 11         | Pierre       | 1    | 2         | 1     | 1       |
| 12         | Troy         | 2    | 3         | 3     | 1       |
| 13         | David        | 3    | 6         | 6     | 2       |
| 14         | Michaela     | 1    | 4         | 5     | 2       |
| 15         | Rose         | 1    | 4         | 6     | 1       |
| 16         | Anita        | 3    | 6         | 6     | 2       |
| 17         | Connor       | 3    | 3         | 3     | 1       |
| 18         | Eddie        | 1    | 2         | 7     | 1       |
| 19         | Karen        | 3    | 5         | 5     | 2       |
| 20         | Rachel       | 3    | 2         | 2     | 1       |
|            |              |      |           |       |         |

Key:

| Item_Type | Item     | Value |
|-----------|----------|-------|
| Meat      | Chicken  | 1     |
| Meat      | Beef     | 2     |
| Meat      | Fish     | 3     |
| Vegetable | Carrots  | 1     |
| Vegetable | Peas     | 2     |
| Vegetable | Corn     | 3     |
| Vegetable | Broccoli | 4     |
| Vegetable | Zucchini | 5     |
| Vegetable | Eggplant | 6     |
| Drink     | Water    | 1     |
| Drink     | Milk     | 2     |
| Drink     | Juice    | 3     |
| Drink     | Cola     | 4     |
| Drink     | Lemonade | 5     |
| Drink     | Tea      | 6     |
| Drink     | Punch    | 7     |
| Dessert   | Cake     | 1     |
| Dessert   | Pie      | 2     |

我正在尝试找出首选项中的每一列项目值的价值标签的最有效方法从 key item 列。 >。换句话说，我想加入两个表以创建下表：

| Student_ID | Student_Name | Meat    | Vegetable | Drink    | Dessert |
|------------|--------------|---------|-----------|----------|---------|
| 1          | Jeff         | Fish    | Carrots   | Cola     | Cake    |
| 2          | Andrea       | Chicken | Carrots   | Juice    | Cake    |
| 3          | Allison      | Fish    | Peas      | Punch    | Cake    |
| 4          | Randy        | Chicken | Carrots   | Cola     | Pie     |
| 5          | Carl         | Beef    | Zucchini  | Milk     | Pie     |
| 6          | Bobby        | Chicken | Eggplant  | Punch    | Pie     |
| 7          | Julie        | Fish    | Zucchini  | Milk     | Cake    |
| 8          | Anna         | Chicken | Eggplant  | Punch    | Pie     |
| 9          | Carlos       | Chicken | Eggplant  | Milk     | Pie     |
| 10         | Roger        | Beef    | Broccoli  | Milk     | Cake    |
| 11         | Pierre       | Chicken | Peas      | Water    | Cake    |
| 12         | Troy         | Beef    | Corn      | Juice    | Cake    |
| 13         | David        | Fish    | Eggplant  | Tea      | Pie     |
| 14         | Michaela     | Chicken | Broccoli  | Lemonade | Pie     |
| 15         | Rose         | Chicken | Broccoli  | Tea      | Cake    |
| 16         | Anita        | Fish    | Eggplant  | Tea      | Pie     |
| 17         | Connor       | Fish    | Corn      | Juice    | Cake    |
| 18         | Eddie        | Chicken | Peas      | Punch    | Cake    |
| 19         | Karen        | Fish    | Zucchini  | Lemonade | Pie     |
| 20         | Rachel       | Fish    | Peas      | Milk     | Cake    |
|            |              |         |           |          |         |

peferences中每个学生的每个进餐项目值匹配从 key 中的单个 item 列中获得其适当的标签。

Answer 1

我不确定您提到的最有效的方法，但是您可以执行 Inner Join 四次，如下：

Select P.Student_ID, P.Student_Name, 
K1.item as Meat, K2.item as Vegetable,K3.item as Drink, K4.item as Dessert
from Preferences P 
inner Join Key_ K1 On (P.Meat=K1.Value_ and K1.Item_Type='Meat')
inner Join Key_ K2 On (P.Vegetable=K2.Value_ and K2.Item_Type='Vegetable')
inner Join Key_ K3 On (P.Drink=K3.Value_ and K3.Item_Type='Drink')
inner Join Key_ K4 On (P.Dessert=K4.Value_ and K4.Item_Type='Dessert')
order by P.Student_ID

请参阅 db-fiddle 。

Answer 2

这是一个带有单个连接的解决方案。
它是在MySQL中实现的，但是有了非常小的更改，它可以在任何其他DB中实现。

select    Preferences.Student_ID
         ,Preferences.Student_Name
         ,max(case `Key`.Item_Type when 'Meat'      then `Key`.item end) as Meat
         ,max(case `Key`.Item_Type when 'Vegetable' then `Key`.item end) as Vegetable
         ,max(case `Key`.Item_Type when 'Drink'     then `Key`.item end) as Drink
         ,max(case `Key`.Item_Type when 'Dessert'   then `Key`.item end) as Dessert
from Preferences join `Key`on 
         `Key`.Item_Type = 'Meat'      and `Key`.value = Preferences.Meat      
      or `Key`.Item_Type = 'Vegetable' and `Key`.value = Preferences.Vegetable 
      or `Key`.Item_Type = 'Drink'     and `Key`.value = Preferences.drink     
      or `Key`.Item_Type = 'Dessert'   and `Key`.value = Preferences.Dessert
group by Preferences.Student_ID, Preferences.Student_Name
order by Preferences.Student_ID
       
       

Student_ID	Student_Name	Meat	Vegetable	Drink	Dessert
1	Jeff	Fish	Carrots	Cola	Cake
2	Andrea	Chicken	Carrots	Juice	Cake
3	Allison	Fish	Peas	Punch	Cake
4	Randy	Chicken	Carrots	Cola	Pie
5	Carl	Beef	Zucchini	Milk	Pie
6	Bobby	Chicken	Eggplant	Punch	Pie
7	Julie	Fish	Zucchini	Milk	Cake
8	Anna	鸡	茄子	打孔	派
9	Carlos	鸡肉	茄子	牛奶	派
10	Roger	牛肉	花	牛奶	蛋糕
11	Pierre	Chicken	Peas	Water	Cake
12	Troy	Beef	Corn	Juice	Cake
13	David	Fish	茄子	茶	馅饼
14	Michaela	Chicken	Broccoli	Lemonade	Pie
15	Cape	Cape	​	​	西兰
​	​	Fish	Chicken	Tea	Pie
17	Connor	Fish	Corn	Juice	Cake
18	Eddie	Chicken	Peas	Punch	Cake
19	Karen	Fish	Zucchini	Lemonade	Pie
20	Rachel	Fish	Peas	Milk	Cake

Answer 3

这是一个基于 undivot＆amp;枢轴。
请注意，仅对于枢轴语句才需要子查询，因为该语句一部分的任何列都成为隐式组的一部分。

select  *
from   (select  item
               ,food_type
               ,Student_ID
               ,Student_Name
        from    Preferences as p
        unpivot (food for food_type in (Meat,Vegetable,Drink,Dessert)) as u 
        join food on food.value =  u.food and  food.Item_Type = u.food_type) as pu
        pivot (max(item) for food_type in(Meat, Vegetable, Drink, Dessert)) as p
order by Student_ID  

Student_ID	Student_Name	Meat	Vegetable	Drink	Dessert
1	Jeff	Fish	Carrots	Cola	Cake
2	Andrea	Chicken	Carrots	Juice	Cake
3	Allison	Fish	Peas	Punch	Cake
4	Randy	Chicken	Carrots	Cola	Pie
5	Carl	Beef	Zucchini	Milk	Pie
6	Bobby	Chicken	Eggplant	Punch	Pie
7	Julie	Fish	Zucchini	Milk	Cake
8	Anna	鸡	茄子	打孔	派
9	Carlos	鸡肉	茄子	牛奶	派
10	Roger	牛肉	花	牛奶	蛋糕
11	Pierre	Chicken	Peas	Water	Cake
12	Troy	Beef	Corn	Juice	Cake
13	David	Fish	茄子	茶	馅饼
14	Michaela	Chicken	Broccoli	Lemonade	Pie
15	Cape	Cape	​	​	西兰
​	​	Fish	Chicken	Tea	Pie
17	Connor	Fish	Corn	Juice	Cake
18	Eddie	Chicken	Peas	Punch	Cake
19	Karen	Fish	Zucchini	Lemonade	Pie
20	Rachel	Fish	Peas	Milk	Cake

小提琴

Answer 4

另一种方法是进行两项操作，首先是a undivot - 然后是内部联接。不确定您是否想再次旋转结果，但是如果您这样做，我想这将是第三个操作。

WITH CTE_UNPIVOT AS (
SELECT * FROM Preferences
UNPIVOT( VALUE for ITEM_TYPE in ( Meat, Veg, Drink, Dessert ))
),
CTE_MAPPED AS (
SELECT * FROM CTE_UNPIVOT
INNER JOIN Key
ON CTE_UNPIVOT.ITEM_TYPE = Key.ITEM_TYPE
)
SELECT * FROM CTE_MAPPED;