合并选择前 1 条 SQL 语句

问题描述

我有 4 个选择语句:

I have 4 select statements:

SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software1' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software2' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software3' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software4' order by auditDate desc

目前，每个 select 语句都返回一个包含一行的表，如下所示:

Currently, each select statement returns a table with one row like this:

   software  | auditDate  | versionNumber
1| software1 | 8/22/2017  | 5.0

如果 top 1 条件不存在，那么结果表将有更多行具有不同 auditDate 和 versionNumber，但我只需要每个软件的最新记录 (auditDate).

If the top 1 condition wasn't there, then the result table would have more rows of the same software with different auditDate and versionNumber, but I only needed the most recent record (auditDate) for each software.

我想将所有这些选择语句合并到一个查询中，该查询返回一个类似于以下的表:

I'd like to roll all these select statements into one query that returns a table similar to this:

   software  | auditDate  | versionNumber
1| software1 | 8/22/2017  | 5.0
2| software2 | 8/20/2017  | 5.3
3| software3 | 8/21/2017  | 4.9
4| software4 | 8/16/2017  | 5.6

其中每一行都与上面的单个 select top 1 语句相同.

where each row is the same as the individual select top 1 statement above.

由于 order by 子句，UNION 似乎不起作用，我对其他解决方案感到困惑.

A UNION doesn't seem to work because of the order by clause and I'm stumped for other solutions.

推荐答案

另一个选项是使用 WITH TIES 子句

Another Option is using the WITH TIES clause

Select top 1 with ties 
       software, auditDate, versionNumber 
 From  table1 
 Where software IN ('software1','software2','software3','software4')
 Order By Row_Number() over (Partition By software Order By auditDate Desc)

这篇关于合并选择前 1 条 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！