时间戳

首页 > 解决方案 > 有没有更有效的方法来返回匹配最大值的记录？

sql - 有没有更有效的方法来返回匹配最大值的记录？

问题描述

我有一张包含感兴趣领域的表格：

Order      Shift    DateTime
23605624   Red      43470.23958
23605624   Yellow   43469.73958
23605624   Green    43469.38542
23605917   Red      43471.26042
23605917   Green    43471.73958
23605953   Red      43470.23958
23605953   Yellow   43469.17083

我想要 [Order] 和 [Shift]，其中 [DateTime] 是每个 [Order] 的最大值。所以上表将转换为：

Order      Shift    DateTime
23605624   Red      43470.23958
23605917   Green    43471.73958
23605953   Red      43470.23958

虽然我不需要 DateTime 字段。

我实际上已经实现了这一点，但是该方法感觉过于复杂（并且查询运行需要相当长的时间）我在 from 子句和其中一个连接中都有子查询。

感觉我应该能够在 where 子句中按照“value=max(value)”的行进行某种连接，但我似乎无法弄清楚，因为子查询已经有一个连接并且还包括一个case 语句，我似乎无法掌握语法。

这是我拥有的完整代码：

SELECT
   max_datetime.[Order],
   max_datetime.[Line],
   max_datetime.[Quantity],
   max_datetime.[UoM],
   shifts.[Shift]

FROM (
   SELECT
      SCMPEH.PRONUM as [Order],
      SCMPEH.LINNUM as [Line],
      SUM(SCMPEH.PEHQTY) as [Quantity],
      SCMPEH.PEHUNT as [UoM],

      MAX(CASE
         WHEN SCMPEH.PEHSTT < '05:45:00'
         THEN SCMPEH.PEHDAT + SCMPEH.PEHSTT + 1
         ELSE SCMPEH.PEHDAT + SCMPEH.PEHSTT
      END) as [DateTime]

   FROM
      SCMPEH

      INNER JOIN SCMLIN on
         (SCMLIN.LINNUM = SCMPEH.LINNUM)

      INNER JOIN SCMSHI on
         (SCMSHI.SHINUM = SCMPEH.SHINUM)

   WHERE (
      SCMPEH.PEHDAT >= '1/1/2019'
   ) AND (
      SCMLIN.LINEXP = 'Y'
   )

   GROUP BY
      SCMPEH.LINNUM,
      SCMPEH.PRONUM,
      SCMPEH.PEHUNT

   ) as max_datetime

INNER JOIN (
   SELECT
      SCMPEH.PRONUM as [Order],
      SCMSHI.SHIDES as [Shift],

      CASE
         WHEN SCMPEH.PEHSTT < '05:45:00'
         THEN SCMPEH.PEHDAT + SCMPEH.PEHSTT + 1
         ELSE SCMPEH.PEHDAT + SCMPEH.PEHSTT
      END as [DateTime]

   FROM
      SCMPEH

      INNER JOIN SCMSHI on
         (SCMSHI.SHINUM = SCMPEH.SHINUM)

   WHERE (
      SCMPEH.PEHDAT >= '1/1/2019'
   )
   ) as shifts on
   (shifts.[Order] = max_datetime.[Order]) AND
   (shifts.[DateTime] = max_datetime.[DateTime])

我最感兴趣的是 [Shift] 字段。

标签： sqlsql-server

解决方案

一种选择是WITH TIES与row_number()

例子

Select top 1 with ties * 
 From  YourTable
 Order By Row_Number() over (Partition By [Order] Order By DateTime desc)

退货

Order       Shift   DateTime
23605624    Red     43470.23958
23605917    Green   43471.73958
23605953    Red     43470.23958

完全披露：使用带有 row_number() 的子查询会提高性能

推荐阅读