SQL Server:如何将 UNION 与两个都有 WHERE 子句的查询一起使用?

问题描述：

鉴于:

两个需要过滤的查询:

select top 2 t1.ID, t1.ReceivedDate
  from Table t1
 where t1.Type = 'TYPE_1'
 order by t1.ReceivedDate desc

还有:

select top 2 t2.ID
  from Table t2
 where t2.Type = 'TYPE_2'
 order by t2.ReceivedDate desc

另外，这些返回我正在寻找的 ID:(13, 11 and 12, 6)

Separately, these return the IDs I'm looking for: (13, 11 and 12, 6)

基本上，我想要两种特定类型数据的两个最新记录.

Basically, I want the two most recent records for two specific types of data.

我想像这样将这两个查询结合在一起:

I want to union these two queries together like so:

select top 2 t1.ID, t2.ReceivedDate
  from Table t1
 where t1.Type = 'TYPE_1'
 order by ReceivedDate desc
union
select top 2 t2.ID
  from Table t2
 where t2.Type = 'TYPE_2'
 order by ReceivedDate desc

问题:

问题是这个查询是无效的，因为第一个 select 不能有 order by 子句，如果它是 unioned.如果没有order by，它就不能有top 2.

The problem is that this query is invalid because the first select cannot have an order by clause if it is being unioned. And it cannot have top 2 without having order by.

我该如何解决这种情况?

How can I fix this situation?

答

您应该能够为它们取别名并用作子查询(您第一次尝试无效的部分原因是因为第一个选择有两列(ID 和 ReceivedDate) 但您的第二个只有一个 (ID) - 此外，Type 是 SQL Server 中的保留字，不能像列名一样使用):

You should be able to alias them and use as subqueries (part of the reason your first effort was invalid was because the first select had two columns (ID and ReceivedDate) but your second only had one (ID) - also, Type is a reserved word in SQL Server, and can't be used as you had it as a column name):

declare @Tbl1 table(ID int, ReceivedDate datetime, ItemType Varchar(10))
declare @Tbl2 table(ID int, ReceivedDate datetime, ItemType Varchar(10))

insert into @Tbl1 values(1, '20010101', 'Type_1')
insert into @Tbl1 values(2, '20010102', 'Type_1')
insert into @Tbl1 values(3, '20010103', 'Type_3')

insert into @Tbl2 values(10, '20010101', 'Type_2')
insert into @Tbl2 values(20, '20010102', 'Type_3')
insert into @Tbl2 values(30, '20010103', 'Type_2')

SELECT a.ID, a.ReceivedDate FROM
 (select top 2 t1.ID, t1.ReceivedDate
  from @tbl1 t1
  where t1.ItemType = 'TYPE_1'
  order by ReceivedDate desc
 ) a
union
SELECT b.ID, b.ReceivedDate FROM
 (select top 2 t2.ID, t2.ReceivedDate
  from @tbl2 t2
  where t2.ItemType = 'TYPE_2'
  order by t2.ReceivedDate desc
 ) b

SQL Server:如何将 UNION 与两个都有 WHERE 子句的查询一起使用?

相关推荐