我有一张这样的桌子
id | SKU | quantity_purchased | discount_price
---------------------------------------------------
1 | abc | 1 | 10.0
2 | abc | 90 | 00
2 | abc | 9 | 00
3 | xyz | 1 | 50.0
3 | xyz | 2 | 50.0
4 | xyz | 100 | 00
4 | xyz | 100 | 00
-----------------------------------------------
我的问题是
select
(select sum(quantity_purchased) from order_details where discount_price > 0.00) as qty_discount,
(select sum(quantity_purchased) from order_details where discount_price = 0.00)as qty_original,
sku
from order_details
GROUP BY sku
我要求的结果是
SKU | quantity_original | quantity_discount
---------------------------------------------------
abc | 1 | 99
xyz | 3 | 200
-----------------------------------------------
也就是说,经过计算,我需要两列相同的
order_details
,我无法建立逻辑,我尝试在嵌套查询中使用
sku
,但它不起作用。。。任何帮助都非常感谢。。
谢谢
更新:
试着通过这个但还是失败了,
select
(select sum(quantity_purchased) from order_details where discount_price > 0.00 ) as qty_discount,
(select sum(quantity_purchased) from order_details where discount_price = 0.00 )as qty_original,
sku
from order_details
where sku = (select distinct sku from order_details)
GROUP BY sku
最佳答案
您可以使用conditional aggregation
来执行以下操作:
select sku,
sum(case when discount_price != 0 then quantity_purchased
else 0
end) quantity_original,
sum(case when discount_price = 0 then quantity_purchased
else 0
end) quantity_discount
from order_details
group by sku
SQL Fiddle Demo
Results:
| SKU | quantity_original | quantity_discount |
|-----|-------------------|-------------------|
| abc | 1 | 99 |
| xyz | 3 | 200 |
关于mysql - 嵌套查询返回值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35818659/