仅按多列联合得最大值（值）

Question

我想在最高时间为每个[组]检索一个结果。

当前代码的结果：

SELECT [Group], ArticleNumber, max(TimeTrue) as Time
FROM PerformanceOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber

UNION

SELECT [Group], ArticleNumber, max(StopTime) as Time
FROM StoppageOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
ORDER BY Time DESC

结果应该只有两条记录（csv）：

Group,ArticleNumber,Time
70521-030,,2021-03-15 13:50:15
Pack2,183026,2021-03-15 13:47:39

Answer 1

尝试在时态表中使用order by选择前1，然后使用union查询它们

SELECT top 1[Group], ArticleNumber, max(TimeTrue) as Time into #tmp1
FROM PerformanceOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
order by Time desc

SELECT top 1 [Group], ArticleNumber, max(StopTime) as Time into #tmp2
FROM StoppageOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
ORDER BY Time DESC
    
select * from #tmp1
union
select * from #tmp2

drop table #tmp1
drop table #tmp2

Answer 2

嗯。..在聚合之前，您似乎希望联合所有:

SELECT [Group], ArticleNumber, max(Time) as Time
FROM ((SELECT [Group], ArticleNumber, TimeTrue as Time
       FROM PerformanceOpc 
       WHERE [Group] IN ('Pack2', '70521-030')
      ) UNION ALL
      (SELECT [Group], ArticleNumber, StopTime as Time
       FROM StoppageOpc
       WHERE [Group] IN ('Pack2', '70521-030')
      )
     ) g
GROUP BY [Group], ArticleNumber;

这将为每个组和文章返回一行，这似乎是您的查询正在做的事情。

如果您确实希望每个组只需要一行，那么您需要row_number()而不是聚合：

SELECT g.*
FROM (SELECT g.*, ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY time DESC) as seqnum
      FROM ((SELECT [Group], ArticleNumber, TimeTrue as Time
             FROM PerformanceOpc 
             WHERE [Group] IN ('Pack2', '70521-030')
            ) UNION ALL
            (SELECT [Group], ArticleNumber, StopTime as Time
             FROM StoppageOpc
             WHERE [Group] IN ('Pack2', '70521-030')
            )
           ) g
      ) g
WHERE seqnum = 1;