mysql - 只有几列联合的最大值（值）

Question

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

当前代码的结果：

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

标签： mysqlsqlpowerbipowerquery

Answer 1

嗯。. . 您似乎想要union all 在汇总之前：

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;