时间戳

首页 > 解决方案 > SQL：我需要为表中的每组数据选择具有最大列的行

mysql - SQL：我需要为表中的每组数据选择具有最大列的行

问题描述

我有一个这样的连接结果表（有更多列）：

+------------+-----------+-----------+---------------+-----------------+
| salesid    | itemid    | confirmid | confirmdocnum | createddatetime |
+------------+-----------+-----------+---------------+-----------------+
| a0001      | foo       |        21 |      2501-1   |   2019-01-02    |
| a0001      | bar       |        21 |      2501-1   |   2019-01-02    |
| a0001      | baz       |        22 |      2501-2   |   2019-01-03    |
| a0001      | foo       |        23 |      2501-3   |   2019-01-04    |
| a0001      | bar       |        23 |      2501-3   |   2019-01-04    |
| a0002      | foo       |        24 |      2502-1   |   2019-01-01    |
| a0002      | bar       |        24 |      2502-1   |   2019-01-01    |
| a0002      | bar       |        25 |      2502-2   |   2019-01-03    |
| a0003      | foo       |        26 |      2503-1   |   2019-01-04    |
| a0003      | bar       |        27 |      2503-2   |   2019-01-07    |
| a0004      | baz       |        28 |      2504-1   |   2019-01-05    |
+------------+-----------+-----------+---------------+-----------------+

我需要检索这样的东西：

+------------+-----------+-----------+---------------+-----------------+
| salesid    | itemid    | confirmid | confirmdocnum | createddatetime |
+------------+-----------+-----------+---------------+-----------------+
| a0001      | foo       |        23 |      2501-3   |   2019-01-04    |
| a0001      | bar       |        23 |      2501-3   |   2019-01-04    |
| a0002      | bar       |        25 |      2502-2   |   2019-01-03    |
| a0003      | bar       |        27 |      2503-2   |   2019-01-07    |
| a0004      | baz       |        28 |      2504-1   |   2019-01-05    |
+------------+-----------+-----------+---------------+-----------------+

我需要每个salesid 的所有行都具有最新的confirmdocnum。最新的 confirmdocnum 可以是 '%-1', '%-2', '%-3', ..., '%-n'

所有具有最新确认文档编号的行始终具有最新的 createddatetime。

我用来获取第一个表的连接是：

SELECT cct.salesid, 
       cct.itemid, 
       ccj.confirmid, 
       ccj.confirmdocnum, 
       ccj.createddatetime
FROM AxDynamics.dbo.custconfirmjour AS ccj
LEFT JOIN AxDynamics.dbo.custconfirmtrans AS cct
ON cct.salesid=ccj.salesid
  AND cct.confirmid=ccj.confirmid
  AND cct.confirmdate=ccj.confirmdate
WHERE ccj.CREATEDDATETIME >= CONVERT (DATE,'2019/01/01')

但我不知道如何只选择我需要的线条。我简化了几个额外的列。这个额外的列有我真正需要的数据。我尝试了GROUP BY近似，但无法获取我需要的行的数据。

标签： mysql

解决方案

你必须GROUP BY salesid,itemid用你MAX(confirmdocnum)的JOIN结果来得到你想要的行。我无法测试它，但这样的东西应该可以工作：

    SELECT t1.* 
    FROM (
    SELECT cct.salesid, 
           cct.itemid, 
           ccj.confirmid, 
           ccj.confirmdocnum, 
           ccj.createddatetime
    FROM AxDynamics.dbo.custconfirmjour AS ccj
    LEFT JOIN AxDynamics.dbo.custconfirmtrans AS cct
    ON cct.salesid=ccj.salesid
      AND cct.confirmid=ccj.confirmid
      AND cct.confirmdate=ccj.confirmdate
    WHERE ccj.CREATEDDATETIME >= CONVERT (DATE,'2019/01/01')
    ) t1 
    INNER JOIN 
    (
    SELECT cct.salesid,cct.itemid,MAX(ccj.confirmdocnum) AS max_confirmdocnum
    FROM AxDynamics.dbo.custconfirmjour AS ccj
    LEFT JOIN AxDynamics.dbo.custconfirmtrans AS cct
    ON cct.salesid=ccj.salesid
      AND cct.confirmid=ccj.confirmid
      AND cct.confirmdate=ccj.confirmdate
    WHERE ccj.CREATEDDATETIME >= CONVERT (DATE,'2019/01/01')
    GROUP BY cct.salesid,cct.itemid
    ) t2 
ON t1.salesid=t2.salesid AND t1.itemid=t2.itemid AND t1.confirmdocnum=t2.max_confirmdocnum

推荐阅读