mysql - 选择按 3 列分组的最新记录

Question

我正在尝试返回按ItemNum 和FeeSched 分组的最新记录的价格，可以消除Customer。我无法理解如何合理地做到这一点。

问题是我加入了大约 5 个包含数十万行的表以最终得到这个结果集。初始查询大约需要一分钟才能运行，并且过去曾出现过一些超时错误的问题。由于这将在客户端的工作站上运行，它可能运行得更慢，而且我无权修改服务器设置以增加内存/超时。

这是我的数据:

Customer        Price            ItemNum           FeeSched             Date
   5            70.75             01202               12             12-06-2017
   5            70.80             01202               12             06-07-2016
   5            70.80             01202               12             07-21-2017
   5            70.80             01202               12             10-26-2016
   5            82.63             02144               61             12-06-2017
   5            84.46             02144               61             06-07-2016
   5            84.46             02144               61             07-21-2017
   5            84.46             02144               61             10-26-2016

我无权创建临时表或 View ，并且在 C 树中没有 @variable 这样的东西，但在大多数方面它的行为类似于 MySql。我想使用 GROUP BY ItemNum, FeeSched 并选择 MAX(Date)。问题是，除非我将 Price 放入 GROUP BY 中，否则我会收到错误消息。

我可以再次运行查询，只需选择 ItemNum、FeeSched、Date，然后执行 INNER JOIN，但每次运行查询都需要一分钟，它似乎有更好的方法，也许我不知道。

这是我正在运行的查询，除了它正在处理的数据量之外，它并没有那么复杂。最终结果约为 50,000 行。我不能分享太多关于数据库结构的信息，因为它包含在保密协议(protocol)中。

SELECT DISTINCT 
CustomerNum,
paid as Price,
ItemNum,
n.pdate as newest
from admin.fullproclog as f
    INNER JOIN ( 
        SELECT 
               id,
               itemId,
               MAX(TO_CHAR(pdate, 'MM-DD-YYYY')) as pdate
        from admin.fullproclog 
        WHERE pdate > timestampadd(sql_tsi_year, -3, NOW())
        group by id, itemId
    ) as n ON n.id = f.id AND n.itemId = f.itemId AND n.pdate = f.pdate

    LEFT join (SELECT itemId AS linkid, ItemNum FROM   admin.itemlist) AS codes ON codes.linkid = f.itemId AND ItemNum >0
    INNER join (SELECT DISTINCT parent_id, 
                   MAX(ins1.feesched) as CustomerNum
    FROM   admin.customers AS p 
          left join admin.feeschedule AS ins1 
                 ON ins1.feescheduleid = p.primfeescheduleid 
          left join admin.group AS c1 
                 ON c1.insid = ins1.feesched 
    WHERE status =1
                 GROUP BY parent_id) 
      AS ip ON ip.parent_id = f.parent_id 

WHERE CustomerNum >0 AND ItemNum >0
UNION ALL

SELECT DISTINCT 
CustomerNum,
secpaid as Price,
ItemNum,
n.pdate as newest
from admin.fullproclog as f
    INNER JOIN ( 
        SELECT 
               id,
               itemId,
               MAX(TO_CHAR(pdate, 'MM-DD-YYYY')) as pdate
        from admin.fullproclog 
        WHERE pdate > timestampadd(sql_tsi_year, -3, NOW())
        group by id, itemId
    ) as n ON n.id = f.id AND n.itemId = f.itemId AND n.pdate = f.pdate

    LEFT join (SELECT itemId AS linkid, ItemNum FROM   admin.itemlist) AS codes ON codes.linkid = f.itemId AND ItemNum >0
    INNER join (SELECT DISTINCT parent_id, 
                   MAX(ins1.feesched) as CustomerNum
    FROM   admin.customers AS p 
          left join admin.feeschedule AS ins1 
                 ON ins1.feescheduleid = p.secfeescheduleid 
          left join admin.group AS c1 
                 ON c1.insid = ins1.feesched 
    WHERE status =1
                 GROUP BY parent_id) 
      AS ip ON ip.parent_id = f.parent_id 

WHERE CustomerNum >0  AND ItemNum >0 

Answer 1

看完前三段感觉还是挺简单的，看完整题就有点糊涂了。

无论您为获取上面发布的数据做了什么，一旦您获得了这样的数据，就很容易检索“按 ItemNum 和 FeeSched 分组的“最新记录” >”。

如何:

• 首先，将整个结果集按Date DESC 排序。
• 其次，从排序后的结果集中选择需要的字段，按ItemNum、FeeSched分组，不采用任何聚合方式。

所以，查询可能是这样的:

SELECT t.Price, t.ItemNum, t.FeeSched, t.Date 
FROM (SELECT * FROM table ORDER BY Date DESC) AS t 
GROUP BY t.ItemNum, t.FeeSched;

工作原理:

当您的数据被分组并且您选择没有聚合方法的行时，它只会返回每组的第一行。由于您在分组之前对所有行进行了排序，因此第一行恰好是“最近的记录”。

如果您对这种方法有任何问题或错误，请与我联系。