主页 / 问题 / 722315
Accepted
Spider
Asked:2020-09-24 01:29:08 +0000 UTC2020-09-24 01:29:08 +0000 UTC

选择 2 个字段中具有最大值的行

  • 772

有一张表，里面有很多行和字段，字段中有字段sub_id，date和status。有没有一种方法可以选择那些date在相同的行中具有最大值的行sub_id，如果突然发现date有多个最大值，那么status在具有相同sub_id和相同最大值的行中选择那些具有最大值的行date？现在我这样做：

SELECT o.`id`,o.`sub_id`, o.`date`, o.`status`
INNER JOIN (SELECT MAX(`date`) `max_date`, `sub_id` FROM `orders` GROUP BY `sub_id`) o2 ON o2.`max_date`=o.`date` AND o2.`sub_id`=o.`sub_id`
FROM `orders` o

这似乎可行，但如果突然sub_id有多个相同的行date，则选择选择“第一个”，但我想选择最大的行status。

是否有可能做到这一点？

mysql

1 个回答

  1. Best Answer
    SELECT o3.*
FROM orders AS o3,
(
    SELECT o2.sub_id, o2.date, MAX(status) AS status
    FROM orders AS o2,
    (
        SELECT o1.sub_id, MAX(o1.date) AS date
        FROM orders AS o1
        GROUP BY o1.sub_id
    ) AS sq1
    WHERE o2.sub_id = sq1.sub_id AND o2.date = sq1.date
    GROUP BY o2.sub_id, o2.date
) AS sq2
WHERE o3.sub_id = sq2.sub_id AND o3.date = sq2.date AND o3.status = sq2.status

    如何通过 sub_id 从与该表相关的另一个表中添加 LEFT JOIN 字段？

    SELECT o3.*, sometable.somefield
FROM orders AS o3
INNER JOIN
(
    SELECT o2.sub_id, o2.date, MAX(status) AS status
    FROM orders AS o2,
    (
        SELECT o1.sub_id, MAX(o1.date) AS date
        FROM orders AS o1
        GROUP BY o1.sub_id
    ) AS sq1 
    WHERE o2.sub_id = sq1.sub_id AND o2.date = sq1.date
    GROUP BY o2.sub_id, o2.date
) AS sq2 ON o3.sub_id = sq2.sub_id AND o3.date = sq2.date AND o3.status = sq2.status
LEFT JOIN sometable ON sometable.sub_id = o3.sub_id
    • 3

相关问题