具有来自其他表的转置值的 MySQL View

Question

我在 MySQL 的 Google 云 sql 数据库中有 2 个表，其中一个有一些引用:

+-------+---------+--------+
| ref   | couleur | pack   |
+-------+---------+--------+
| HELLO | NOIR    | 3S2M2L |
| HELLO | WHITE   | 3S2M2L |
| WORLD | RED     | 3SM2ML |
| WORLD | GREEN   | 3SM2ML |
+-------+---------+--------+

一个有一些包:

+--------+------+------+------+------+------+
| pack   | S    | M    | L    | SM   | ML   |
+--------+------+------+------+------+------+
| 3S2M2L |    3 |    2 |    2 |    0 |    0 |
| 3SM2ML |    0 |    0 |    0 |    3 |    2 |
+--------+------+------+------+------+------+

我需要一个包含尺寸的现有引用列表:

+-------+---------+--------+
| ref   | couleur | taille |
+-------+---------+--------+
| HELLO | NOIR    | S      |
| HELLO | NOIR    | M      |
| HELLO | NOIR    | L      |
| HELLO | WHITE   | S      |
| HELLO | WHITE   | M      |
| HELLO | WHITE   | L      |
| WORLD | RED     | SM     |
| WORLD | RED     | ML     |
| WORLD | GREEN   | SM     |
| WORLD | GREEN   | ML     |
+-------+---------+--------+

我正在尝试这样的情况，但似乎当我添加另一个“情况下”替换“空”时，它给了我一个错误。

select 
    r.ref, 
    r.couleur,
    (case when p.S > 0 then '  S' else null end
    ) as 'taille'
from _refs r
left join _packs p on r.pack = p.pack
;

我有点开始使用 SQL，非常感谢您的帮助。

Answer 1

衣服的尺寸(？)在您的数据库模型中是硬编码的。解决方案是合并五个单独的查询，如:

select * from (
  select r.ref, r.couleur, 'S' as taille 
    from _refs r join _packs p on p.pack = r.pack and p.s > 0
  union
  select r.ref, r.couleur, 'M'
    from _refs r join _packs p on p.pack = r.pack and p.m > 0
  union
  select r.ref, r.couleur, 'L'
    from _refs r join _packs p on p.pack = r.pack and p.l > 0
  union
  select r.ref, r.couleur, 'SM'
    from _refs r join _packs p on p.pack = r.pack and p.sm > 0
  union
  select r.ref, r.couleur, 'ML'
    from _refs r join _packs p on p.pack = r.pack and p.ml > 0
) x
order by ref, couleur, taille