如何影响组到其他第二个联接表

Question

我有一些像这样的表

request_buys

| id | invoice           | user_id |
| -- | ----------------- | ------- |
| 3  | 20220405/01104298 | 1       |

表 traces

| id | request_buy_id | status_id | created_at          |
| -- | -------------- | --------- | ------------------- |
| 37 | 3              | 1         | 2022-03-27 14:12:25 |
| 38 | 3              | 2         | 2022-03-28 14:12:25 |
| 39 | 3              | 3         | 2022-03-29 14:12:25 |
| 40 | 3              | 4         | 2022-03-30 14:12:25 |
| 41 | 3              | 5         | 2022-03-31 14:12:25 |
| 42 | 3              | 6         | 2022-04-01 14:12:25 |

表 statuses

| id | nama              |
| -- | ----------------- |
| 1  | Order Placed      |
| 2  | Order Paid        |
| 3  | Accepted          |
| 4  | Picked by Courier |
| 5  | In Transit        |
| 6  | Delivered         |
| 7  | Rated             |
| 8  | Rejected          |
| 9  | Canceled          |

然后我尝试设计如下查询

select
 request_buys.invoice,
 MAX(traces.id) as traces_id,
 MAX(statuses.nama) as statuses_nama
from 
 `request_buys`
 inner join `traces` on `request_buys`.`id` = `traces`.`request_buy_id`
 inner join `statuses` on `traces`.`status_id` = `statuses`.`id`
where
 `user_id` = 1
group by
 request_buys.id

并生成如下所示的输出

输出

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Picked by Courier |

和我期望的输出应该如下表

期望

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Delivered         |

我知道我的错误是在MAX(statuses.nama)中，我应该改变它，就像删除状态中的MAX()一样。 nama

但我只是收到错误像这样“SELECT列表不在GROUP BY子句中并且包含非聚合...这与sql_mode=only_full_group_by不兼容”

然后我尝试使用如下查询清除值“ONLY_FULL_GROUP_BY”

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

，结果如下

输出

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Order Placed      |

，我真的很困惑 以及如何使基于request_buys.id的“GROUP BY”结果中的trace_id.status_id仍然与statuses.id有关系

Answer 1

您的问题在于您滥用max（statuses.nama）表达式。根据您的预期输出，您打算获得与最大值（traces.id）相匹配的status.nama，而不是最大值（statuses.nama）值，该值以字母顺序返回最高值。在这种情况下，首字母“ p”＆gt; 'D'。我已经对您的代码进行了一些调整，并在Workbench上尝试了它，假设特定用户有多个发票（例如插入request_buys values（4，'20230405/01104298，1）;插入轨迹值中（43,4,7，'2022-04-01 14:12:25'）;）它按预期工作。

select invoice, t.id as traces_id, s.nama as statuses_name from request_buys r 
join traces t on r.id=t.request_buy_id
join statuses s on t.status_id=s.id
join
    (select traces.request_buy_id, MAX(traces.id) as traces_id
    from `request_buys`
    inner join `traces` on `request_buys`.`id` = `traces`.`request_buy_id`
    where
    `user_id` = 1
    group by
    traces.request_buy_id ) join_t
    on t.request_buy_id=join_t.request_buy_id and t.id=join_t.traces_id
;

Answer 2

如果我正确理解，您正在尝试检索每张发票的最新状态。使用max（nama）不会返回结果，因为它只是选择最大状态名称​​字母。

假设您使用MySQL 8.X，请使用Row_number（）在最新日期之前先对每个发票的状态进行排序和排名。然后使用抓住最新的rownum = 1

WITH cte AS (

    SELECT rb.id AS request_buy_id
            , rb.invoice
            , t.id AS traces_id
            , s.nama AS statuses_nama
            , ROW_NUMBER() OVER(PARTITION BY rb.id ORDER BY t.created_at DESC) AS RowNum
    FROM   request_buys rb
             INNER JOIN traces t ON rb.id = t.request_buy_id
             INNER JOIN statuses s ON t.status_id = s.id
    WHERE  user_id = 1
)
SELECT * 
FROM   cte 
WHERE  RowNum = 1
;

结果：

request_buy_id	invoice	traces_id	statuses_nama	rownum
3	20220405/01104298	42	交付	1

db＆lt; “ https://dbfiddle.uk/?rdbms = mysql_8.0&； fiddle = b88fc799cf26157f5ac99683c444444cf3“ rel =“ nofollow noreferrer”