SQL Server:如何使用带有WHERE子句的联合查询?

Given:

考虑到:

Two queries that require filtering:

需要过滤的两个查询:

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

And:

和:

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

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

另外，这些返回我正在寻找的id:(13, 11和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

Problem:

问题:

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.

问题是这个查询无效，因为如果第一个select正在进行联合，那么它就不能具有order by子句。它不可能没有top 2而没有order by。

How can I fix this situation?

我怎样才能解决这个问题呢?

7 个解决方案

#1

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):

你应该能够别名,使用子查询(部分原因你的第一次努力是无效的,因为第一个选择有两个列(ID和ReceivedDate),但是你的第二个也只有一个(ID),类型是一个保留字在SQL Server,并不能作为你一个列名称):

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

#2

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

or using CTE (SQL Server 2005+)

或使用CTE (SQL Server 2005+)

;with One as
(
    select top 2 t1.ID, t1.ReceivedDate
    from Table t1
    where t1.Type = 'TYPE_1'
    order by t1.ReceivedDate de
)
,Two as
(
    select top 2 t2.ID
    from Table t2
    where t2.Type = 'TYPE_2'
    order by t2.ReceivedDate desc
)
select * from One
union
select * from Two

#3

declare @T1 table(ID int, ReceivedDate datetime, [type] varchar(10))
declare @T2 table(ID int, ReceivedDate datetime, [type] varchar(10))

insert into @T1 values(1, '20010101', '1')
insert into @T1 values(2, '20010102', '1')
insert into @T1 values(3, '20010103', '1')

insert into @T2 values(10, '20010101', '2')
insert into @T2 values(20, '20010102', '2')
insert into @T2 values(30, '20010103', '2')

;with cte1 as
(
  select *,
    row_number() over(order by ReceivedDate desc) as rn
  from @T1
  where [type] = '1'
),
cte2 as
(
  select *,
    row_number() over(order by ReceivedDate desc) as rn
  from @T2
  where [type] = '2'
)
select *
from cte1
where rn <= 2
union all
select *
from cte2
where rn <= 2

#4

The basic premise of the question and the answers are wrong. Every Select in a union can have a where clause. It's the ORDER BY in the first query that's giving yo the error.

问题和答案的基本前提是错误的。联合中的每个选择都可以有where子句。在第一个查询中给出错误的顺序。

#5

The answer is misleading because it attempts to fix a problem that is not a problem. You actually CAN have a WHERE CLAUSE in each segment of a UNION. You cannot have an ORDER BY except in the last segment. Therefore, this should work...

答案具有误导性，因为它试图解决一个不是问题的问题。你实际上可以在一个联合的每个部分中有一个WHERE子句。除了最后一段，你不可能有顺序。因此,这应该工作……

select top 2 t1.ID, t1.ReceivedDate
from Table t1
where t1.Type = 'TYPE_1'
-----remove this-- order by ReceivedDate desc
union
select top 2 t2.ID,  t2.ReceivedDate --- add second column
  from Table t2
 where t2.Type = 'TYPE_2'
order by ReceivedDate desc

#6

Create views on two first "selects" and "union" them.

在两个“选择”和“联合”视图上创建视图。

#7

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. you are selecting

注意，联合中的每个SELECT语句必须具有相同数量的列。这些列还必须具有类似的数据类型。而且，每个SELECT语句中的列必须以相同的顺序排列。你选择

t1.ID, t2.ReceivedDate from Table t1

t1。ID、t2。从表t1 ReceivedDate

union

联盟

t2.ID from Table t2

t2。从表ID t2

which is incorrect.

这是不正确的。

so you have to write

所以你得写下来

t1.ID, t1.ReceivedDate from Table t1 union t2.ID, t2.ReceivedDate from Table t1

t1。ID、t1。从表t1 union t2接收。ID、t2。从表t1 ReceivedDate

you can use sub query here

您可以在这里使用子查询

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

so it will return only distinct values by default from both table.

默认情况下，它只返回两个表中不同的值。