SQL在列上选择具有最大值的JOIN中的行

一尘不染

SQL在列上选择具有最大值的JOIN中的行

sql

我正在从 3 个不同的表中选择值来概览一些产品订单。没有MAX，没有问题。

这是我正在使用的数据：

-- limited to first rows for the sake of the exemple
+------+---------------------+-------------------------------+-------+
| ID   | post_date           | order_item_name               | price |
+------+---------------------+-------------------------------+-------+
| 2348 | 2019-01-23 18:47:34 | product A                     | 18.9  |
| 2348 | 2019-01-23 18:47:34 | Product B                     | 4.5   |
| 2348 | 2019-01-23 18:47:34 | Product C                     | 50.5  |
| 2349 | 2019-01-23 21:59:04 | Product E                     | 26.5  |
| 2352 | 2019-01-24 07:41:12 | Product C                     | 50.5  |
+------+---------------------+-------------------------------+-------+

这些由以下 SQL 查询返回。

SELECT 
    p.ID AS order_id,
    post_date,
    order_item_name,
    meta_value as price
FROM wp_posts AS p
JOIN wp_woocommerce_order_items
ON p.ID = order_id
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal';

现在我想要的是从每个订单中只获得最昂贵的产品。很明显，只使用MAX函数 withGROUP BY每个订单返回一行，但产品名称与价格不匹配。

SELECT 
    p.ID AS order_id,
    post_date,
    order_item_name,
    MAX(meta_value) AS price
FROM alpha_posts AS p
JOIN alpha_woocommerce_order_items
ON p.ID = order_id
JOIN alpha_woocommerce_order_itemmeta
ON alpha_woocommerce_order_items.order_item_id = alpha_woocommerce_order_itemmeta.order_item_id
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal'
GROUP BY order_id;

这将返回最高价格，但该order_item_name 列与给定价格不对应。

+----------+---------------------+-------------------------------+-------+
| order_id | post_date           | order_item_name               | price |
+----------+---------------------+-------------------------------+-------+
|     2348 | 2019-01-23 18:47:34 | Product A                     | 50.5  | -- should be product C
|     2349 | 2019-01-23 21:59:04 | Product B                     | 26.5  | -- product b is 4.5, so it's clearly not matching (same for the following results)
|     2352 | 2019-01-24 07:41:12 | Product A                     | 60.9  |
|     2354 | 2019-01-25 07:43:36 | Product C                     | 23.1  |
|     2355 | 2019-01-26 19:59:31 | Product D                     | 79.9  |
+----------+---------------------+-------------------------------+-------+

我已经设法找到单表查询的示例，但我对这个多连接查询无能为力。

阅读 191

2021-05-30

共1个答案

一尘不染

不出所料，聚合函数可以在一个列上工作而无需考虑其他列，它们不是过滤器。

这就是为什么该MAX函数返回指定列中遇到的最大值，而其他列不是与所选最大值（或聚合函数的任何结果）对应的列。

为了根据最大值选择匹配的列，我们可以使用JOIN查询，在我们的例子中，连接 onorder_id和price。

SELECT 
    ID, 
    post_date,
    wp_woocommerce_order_items.order_item_name,
    wp_woocommerce_order_itemmeta.meta_value
FROM wp_posts
JOIN wp_woocommerce_order_items
ON ID = order_id
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
JOIN (
    SELECT
        order_id,
        MAX(meta_value) as price
    FROM wp_woocommerce_order_items
    JOIN wp_woocommerce_order_itemmeta
    ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
    WHERE meta_key = '_line_subtotal'
    GROUP BY order_id
) b
ON ID = b.order_id AND wp_woocommerce_order_itemmeta.meta_value = price
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal';

2021-05-30