SQL - 多个内部联接，最近

I am wondering why the below SQL query does not work properly. I am attempting to return the fields from table 1 and table 2 based on the most recent date AND only those elements in those tables that have the name Steve from a third table.

我想知道为什么下面的SQL查询不能正常工作。我试图根据最近的日期返回表1和表2中的字段,并且仅从那些表中具有名称Steve的第三个表中的那些元素返回。

This query, meanwhile, does not actually limit the results to those with the name of Steve. If I remove the second Inner Join and focus on fields only in Table 1 to limit the universe, it works fine.

同时,此查询实际上并不将结果限制为名为Steve的结果。如果我删除第二个内连接并仅关注表1中的字段来限制Universe,它可以正常工作。

Appreciate your help on this. I am using Microsft SQL Server Management Studio.

感谢你对此的帮助。我正在使用Microsft SQL Server Management Studio。

   Select * 
    From [db].table1
    INNER JOIN [db].table2 ON table1.id=table2.id
    INNER JOIN [db].table3 ON table1.id=table3.id

    WHERE (table1.AsOfDate=(SELECT MAX(AsOfDate) from [db].table1)) 
    and table3.Name = 'Steve'

2 个解决方案

#1

The ID's may not be referring to the same ID across all three tables. Your joins assumes that is the case though. I mirrored your query with sample temp tables and your query works.

ID可能不是指所有三个表中的相同ID。你的联接假设是这种情况。我使用示例临时表镜像您的查询,您的查询工作。

--SAMPLE TABLES
IF object_id('tempdb..#table1') is not null drop table #table1
if object_id('tempdb..#table2') is not null drop table #table2
if object_id('tempdb..#table3') is not null drop table #table3

CREATE TABLE #table1 (id INT, my_date date) 
INSERT INTO #table1 (id, my_date) VALUES
(1, '1/1/2018'),
(2, '1/2/2018'),
(3, '1/1/2018')

CREATE TABLE #table2 (id INT, some_field VARCHAR(10)) 
INSERT INTO #table2 (id, some_field) VALUES
(1, 'abc'),
(2, 'xyz'),
(3, 'foo')

CREATE TABLE #table3 (id INT, name VARCHAR(10)) 
INSERT INTO #table3 (id, name) VALUES
(1, 'jon'),
(2, 'steve'),
(3, 'jane')


--QUERY
SELECT * 
FROM #table1 AS x
    INNER JOIN 
    #table2 AS y ON x.id=y.id
    INNER JOIN 
    #table3 AS z ON z.id=x.id
WHERE x.my_date=(SELECT MAX(my_date) from #table1)
    and z.name = 'Steve'

output

id  my_date    id   some_field  id  name
2   2018-01-02  2   xyz        2    steve

#2

I think the simplest way is a window function in the order by:

我认为最简单的方法是按顺序的窗口函数:

Select top (1) with ties . . .  -- list the columns explicitly 
from [db].table1 t1 join
     [db].table2 t2
     on t1.id = t2.id join
     [db].table3 t3
     on t1.id = t3.id
where t3.Name = 'Steve'
order by rank() over (order by t1.AsOfDate);

#1