有没有一种方法可以组合两个MySQL查询,为每列显示一个唯一值和一个AVG值?如在此查询中:
SELECT `price`, `cost`, `shipping`
FROM `orders`
WHERE `year` = 2019 AND `product_id` = 5 AND `order_id` = 77
LIMIT 1
带有
SELECT AVG(`price`), AVG(`cost`), AVG(`shipping`)
FROM `orders`
WHERE `year` = 2019 AND `product_id` = 5
我一直在玩联合和联接,但我找不到在同一查询中返回这些数据的方法。(我可以进行两个单独的查询,并将结果数据并排放置,但如果可能的话,我更喜欢使用一个查询。)
有什么想法吗?
由于两个查询都只返回一条记录,您可以将它们转换为子查询并CROSS JOIN
它们:
SELECT a.price, a.cost, a.shipping, avg.price, avg.cost, avg.shipping
FROM
(
SELECT `price`, `cost`, `shipping`
FROM `orders`
WHERE `year` = 2019 AND `product_id` = 5 AND `order_id` = 77
LIMIT 1
) a
CROSS JOIN (
SELECT AVG(`price`) price, AVG(`cost`) cost, AVG(`shipping`) shipping
FROM `orders`
WHERE `year` = 2019 AND `product_id` = 5
) avg
第一个子查询中LIMIT 1
子句的用途尚不清楚:由于没有ORDER BY
,如果有多个匹配,则不可预测将返回哪个记录。
这里有一种使用条件聚合的替代方法(如果存在多个订单id为77
的记录,则将显示每列的最大值):
SELECT
MAX(CASE WHEN `order_id` = 77 THEN `price` END) price,
MAX(CASE WHEN `order_id` = 77 THEN `cost` END) cost,
MAX(CASE WHEN `order_id` = 77 THEN `shipping` END) shipping,
AVG(`price`) avg_price,
AVG(`cost`) avg_cost,
AVG(`shipping`) avg_shipping
FROM `orders`
WHERE `year` = 2019 AND `product_id` = 5