SQLITE - 正确地将行转换为列

Question

我有一个包含产品订单表的数据库，如下所示:

order_id | prod_code | prod_color | size | quantity |  
-----------------------------------------------------
1          SHIRT       001          S      10
1          SHIRT       001          M      7
1          SHIRT       001          L      8
1          SHIRT       001          XL     1
1          SHIRT       995          S      2
1          SHIRT       995          M      1
1          SHIRT       995          L      0
1          SHIRT       995          XL     1
2          PANTS       ....

和这样的产品价格表:

prod_code | prod_color | price | currency
-----------------------------------------
SHIRT       001          10      EUR
SHIRT       001          9       USD
SHIRT       001          50      YEN
SHIRT       001          15      RUB
SHIRT       995          20      EUR
SHIRT       995          29      USD
SHIRT       995          100     YEN
SHIRT       995          45      RUB 
PANTS       ....  

我想要得到的是这样的 View (为简单起见，按 order_id 过滤):

order_id | prod_code | prod_color | size | quantity | EUR | USD | YEN | RUB
---------------------------------------------------------------------------
1          SHIRT       001          S      10         10    9     50    15
1          SHIRT       001          M      7          10    9     50    15
1          SHIRT       001          L      8          10    9     50    15
1          SHIRT       001          XL     1          10    9     50    15
1          SHIRT       995          S      2          20    29    100   45
1          SHIRT       995          M      1          20    29    100   45
1          SHIRT       995          L      0          20    29    100   45
1          SHIRT       995          XL     1          20    29    100   45

我已经通过网络和 SO 进行了查看，我找到了 THIS QUESTION这准确地向我展示了我需要做什么，但我认为需要进行一些修改...

我尝试在数据库上运行这个查询:

SELECT o.order_id, o.prod_code, o.prod_color, o.size, o.quantity,
MAX(CASE WHEN p.currency = 'EUR' THEN p.price END) AS 'EUR',
MAX(CASE WHEN p.currency = 'USD' THEN p.price END) AS 'USD',
MAX(CASE WHEN p.currency = 'YEN' THEN p.price END) AS 'YEN',
MAX(CASE WHEN p.currency = 'RUB' THEN p.price END) AS 'RUB'
FROM products_order o JOIN products_prices p ON o.prod_code = p.prod_code
WHERE o.order_id = 1
GROUP BY o.order_id, o.prod_code, o.prod_color, o.size, o.quantity

将在价格列中为每一行返回在整个列中找到的最大值:因此对于 [product_color = 001] 和 [product_color = 995] 我的价格都等于 20 欧元(001 是更便宜，只有 10 欧元!)

我也尝试过不使用 MAX，但它为每个价格提供一行，留下很多单元格为空(然后我明白了 MAX 的用途 :D)。

你知道一种方法来做我想做的事吗？如何在单个 prod_color 中使用 MAX 而不是通过每个 prod_color？

我希望你能理解我写的东西，提前致谢，感谢任何帮助。

(如果您需要更清楚地解释某些内容，请直接提问，我会尽快回复。

再次感谢，致以最诚挚的问候

Answer 1

我添加了一个额外的 JOIN 条件:AND o.prod_color = p.prod_color

http://sqlfiddle.com/#!5/fadad/5

SELECT
  o.order_id, o.prod_code, o.prod_color, o.size, o.quantity,
  MAX(CASE WHEN p.currency = 'EUR' THEN p.price END) AS 'EUR',
  MAX(CASE WHEN p.currency = 'USD' THEN p.price END) AS 'USD',
  MAX(CASE WHEN p.currency = 'YEN' THEN p.price END) AS 'YEN',
  MAX(CASE WHEN p.currency = 'RUB' THEN p.price END) AS 'RUB'

FROM products_order o
JOIN products_prices p
  ON o.prod_code = p.prod_code
 AND o.prod_color = p.prod_color /* this line is added */

WHERE o.order_id = '1'
GROUP BY
  o.order_id,
  o.prod_code,
  o.prod_color,
  o.size,
  o.quantity

没有 GROUP BY 的简化示例查询显示了不同之处:

http://sqlfiddle.com/#!5/fadad/13