問題描述

我有 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模板網！