根据另一列的值对一列进行过滤

Suppose I have a table, TestTable, that looks like this:

假设我有一个表，TestTable，它是这样的:

|-------------------|------------------|------------------|
|        Id         |    EventDate     |     EventCode    |
|-------------------|------------------|------------------|
|         1         |    2017-04-10    |        42        |
|-------------------|------------------|------------------|
|         2         |    2017-10-32    |        50        |
|-------------------|------------------|------------------|
|         3         |    2017-06-15    |        60        |
|-------------------|------------------|------------------|
|         4         |    2017-07-13    |        10        |
|-------------------|------------------|------------------|

I want to select only events that took place at a later Date than EventCode 60. Obviously, I could just create a temporary variable and use a WHERE clause:

我希望只选择发生在稍后日期的事件，而不是EventCode 60。显然，我可以创建一个临时变量并使用WHERE子句:

DECLARE @TestEventDate DATETIME;
SET @TestEventDate = 
    (SELECT TOP 1 EventDate
    FROM TestTable
    WHERE EventCode = 60);
SELECT *
FROM TestTable
WHERE EventDate > @TestEventDate

But I can't help but think here should be a way to do this without running multiple SELECT statements. Can I filter indirectly within the same SELECT, mapping an EventDate to an EventCode in the where clause without setting the temporary variable?

但是我忍不住想，这里应该有一种不用运行多个SELECT语句的方法。我是否可以在相同的选择中进行间接过滤，将EventDate映射到where子句中，而不设置临时变量?

Here's the above set to a temporary table so that you can just run it straight in your SSMS:

这是上面设置的临时表，您可以直接在SSMS中运行:

DECLARE @TestTable TABLE(Id INT, EventDate DATETIME, EventCode INT);
INSERT INTO @TestTable(Id, EventDate, EventCode) VALUES (1, '20170410', 42);
INSERT INTO @TestTable(Id, EventDate, EventCode) VALUES (2, '20171030', 50);
INSERT INTO @TestTable(Id, EventDate, EventCode) VALUES (3, '20170615', 60);
INSERT INTO @TestTable(Id, EventDate, EventCode) VALUES (4, '20170713', 10);

DECLARE @TestEventDate DATETIME;
SET @TestEventDate = 
    (SELECT TOP 1 EventDate
    FROM @TestTable
    WHERE EventCode = 60);
SELECT *
FROM @TestTable
WHERE EventDate > @TestEventDate

3 个解决方案

#1

If EventCode is unique, you can do :

如果EventCode是唯一的，您可以:

SELECT T2.*
FROM TestTable AS T
INNER JOIN TestTable AS T2 ON (T.eventdate > T2.eventdate)
WHERE T.EventCode = 60

#2

It is possible to do the whole thing in a join. Basically you want to join all the records that fit the description to the record you want like this:

在一个连接中做整个事情是可能的。基本上你想要加入所有符合你想要的记录的记录:

DECLARE @TestTable TABLE(Id INT, EventDate DATETIME, EventCode INT);

INSERT INTO @TestTable
    (Id,  EventDate, EventCode) 
VALUES 
    ( 1, '20170410',        42),
    ( 2, '20171030',        50),
    ( 3, '20170615',        60),
    ( 4, '20170713',        10);   

SELECT
    T1.*
FROM
    @TestTable T1
    JOIN @TestTable T2
        ON T1.EventDate > T2.EventDate
        AND T2.EventCode = 60

#3

One method is a subquery:

一种方法是子查询:

SELECT *
FROM TestTable
WHERE EventDate > (SELECT TOP 1 EventDate FROM TestTable WHERE EventCode = 60);

You could also use window functions:

你也可以使用窗口功能:

select tt.*
from (select tt.*,
             max(case when eventcode = 60 then eventdate end) over () as eventdate_60
      from testtable tt
     ) tt
where eventdate > eventdate_60;

#1