SQLITE-将行正确转换为列 [英] SQLITE - transposing rows into columns properly
问题描述
我有一个数据库，其中包含一个产品订单表，如下所示：
order_id | prod_code | prod_color |尺寸数量
---------------------------------------------- -------
1衬衫001 S 10
1衬衫001 M 7
1衬衫001 L 8
1衬衫001 XL 1
1衬衫995 S 2
1衬衫995 M 1
1衬衫995 L 0
1衬衫995 XL 1
2裤子....
和这样的产品价格表：
prod_code | prod_color |价格|货币
-----------------------------------------
衬衫001 10欧元
衬衫001 9美元
衬衫001 50日元
衬衫001 15卢布
衬衫995 20欧元
衬衫995 29美元
衬衫995 100日元
衬衫995 45擦
裤子....
我该怎么办想要得到一个这样的视图（为简单起见，按order_id过滤）：
order_id | prod_code | prod_color |尺寸数量欧元|美元|日元|
卢布--------------------------------------------- ------------------------------
1衬衫001 S 10 10 9 50 15
1衬衫001 M 7 10 9 50 15
1衬衫001 L 8 10 9 50 15
1衬衫001 XL 1 10 9 50 15
1衬衫995 S 2 20 29 100 45
1衬衫995 M 1 20 29 100 45
1衬衫995 L 0 20 29 100 45
1衬衫995 XL 1 20 29 100 45
我已经通过网络和SO进行了查找，发现这个问题可以准确地向我显示我需要做的事情，但我认为需要进行一些修改…
我试图在数据库上运行此查询：
选择o.order_id，o.prod_code，o.prod_color，o.size，o.quantity，
MAX（在p.currency ='EUR'THEN p.price END的情况下）作为'EUR'
MAX（当p.currency ='USD'然后p.price结束时的情况）作为'USD'，
MAX（p.currency ='Yen'然后p.price结束时的情况）作为'YEN '，
MAX（在p.currency ='RUB'然后p.price结束的情况下）作为'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的情况，但是每个价格都给了我一行，所以很多单元格都为空（然后我明白了
您知道做我想做的事情的方法吗？
我希望如何在单个prod_color中使用MAX？
我希望您能理解我写的内容，在此先感谢您的帮助。
>（如果您需要更清楚地说明问题，请提出问题并尽快答复。
再次感谢您，最好的问候
我添加了一个额外的 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（以p.currency ='EUR'然后p.price END）为例'EUR '，
MAX（当p.currency ='USD'然后p.price结束时的情况）AS'USD'，
MAX（p.currency ='YEN'THEN p.price结束时的情况）AS 'YEN'，
最大（以p.currency ='RUB'然后p.price结束为例）为'RUB'
FROM products_order o
JOIN products_prices p
在o.prod_code = p.prod_code
AND o.prod_color = p.prod_color / *添加此行* /
W.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
I've a database containing a table for products order like this:
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 ....
and a products prices table like this:
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 ....
what I'd like to get, is a view like this (for simplicity filtered by 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
I already looked through the web and through SO, and I found THIS QUESTION that shows me exactly what I need to do, but I think with some modifications...
I tryed to run this query on the database:
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
will return in the prices columns, for every row, the MAX value found in the entire column: so both for [product_color = 001] and for [product_color = 995] I have price equals to 20 EURO (when for 001 it's cheaper, only 10 euro!)
I tryed also without MAX, but it gives me one row for each price, leaving lot of cells empty (then I understood what MAX is used for :D).
Do you know a way to do what I'm trying to do? How can I use MAX inside a single prod_color instead through every prod_color?
I hope you can understand what I wrote, thanks in advance, any help is appreciated.
(if you need something explained more clearly, just ask and asap I'll reply.
Thanks again, best regards
I added an extra JOIN condition: 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
Simplified example queries without the GROUP BY shows the difference:
http://sqlfiddle.com/#!5/fadad/13
这篇关于SQLITE-将行正确转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
