SQL - 其中日期 = 最大日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38376378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - where date = max date
提问by boHymanh
I've been trying to figure this one out for a while but nothing seems to work for me and maybe a bit of assistance will help me understand it better. I'm joining multiple tables but only want the max date associated with an ID number. There could be multiple dates associated with an ID number. If I take out the MAX(Date) piece, it will run but return multiple date values. I don't want the MAX(Date) to be in the initial select but only within the WHERE clause. Here's my query:
我一直在尝试解决这个问题，但似乎对我没有任何帮助，也许一些帮助会帮助我更好地理解它。我加入了多个表，但只想要与 ID 号关联的最大日期。一个 ID 号可能有多个日期。如果我取出 MAX(Date) 块，它将运行但返回多个日期值。我不希望 MAX(Date) 在初始选择中，而只在 WHERE 子句中。这是我的查询：
SELECT DISTINCT A.ID , C.Date
FROM IDTable A
INNER JOIN AccountTable B
ON B.AccountID = A.AccountID
FULL OUTER JOIN Updates U
ON U.ID = A.ID
FULL OUTER JOIN Update2 C
ON C.AccountID = B.AccountID
INNER JOIN UserTable UT
ON UT.UserMnemonicID = A.AssignedTo
WHERE A.StatusID NOT IN ('Complete','Open')
--Need help here to only return MAX date value:
AND C.Date=(SELECT MAX(C.Date) FROM Update2)
ORDER BY A.ID
回答by Lost in Alabama
If you don't want the MAX(date) in the initial Select then this would work:
如果您不想要初始 Select 中的 MAX(date) 那么这将起作用：
SELECT DISTINCT A.ID , C.Date
FROM IDTable A
INNER JOIN AccountTable B
ON B.AccountID = A.AccountID
FULL OUTER JOIN Updates U
ON U.ID = A.ID
FULL OUTER JOIN Update2 C
ON C.AccountID = B.AccountID
INNER JOIN UserTable UT
ON UT.UserMnemonicID = A.AssignedTo
WHERE A.StatusID NOT IN ('Complete','Open')
AND C.Date=
(SELECT MAX(C2.Date)
FROM Update2 C2
WHERE C2.AccountID = C.AccountID)
ORDER BY A.ID
回答by Gordon Linoff
I think you just want aggregation:
我认为你只是想要聚合：
SELECT A.ID, MAX(C.Date)
FROM IDTable A INNER JOIN
AccountTable B
ON B.AccountID = A.AccountID FULL OUTER JOIN
Updates U
ON U.ID = A.ID FULL OUTER JOIN
Update2 C
ON C.AccountID = B.AccountID INNER JOIN
UserTable UT
ON UT.UserMnemonicID = A.AssignedTo
WHERE A.StatusID NOT IN ('Complete', 'Open')
GROUP BY A.ID;
I have no idea why you would be using FULL OUTER JOIN, much less why you would be mixing them with INNER JOIN. I suspect you really want LEFT JOIN.
我不知道您为什么要使用FULL OUTER JOIN，更不用说为什么要将它们与INNER JOIN. 我怀疑你真的想要LEFT JOIN。
回答by TheGameiswar
In SQL Server ,use ROWNUMBER..
在 SQL Server 中，使用 ROWNUMBER..
;with cte
as
(
SELECT A.ID , C.Date,row_number() over (partition by a.id order by c.date desc) as rn
FROM IDTable A
INNER JOIN AccountTable B
ON B.AccountID = A.AccountID
FULL OUTER JOIN Updates U
ON U.ID = A.ID
FULL OUTER JOIN Update2 C
ON C.AccountID = B.AccountID
INNER JOIN UserTable UT
ON UT.UserMnemonicID = A.AssignedTo
WHERE A.StatusID NOT IN ('Complete','Open')
)
select id,date from cte where rn=1
回答by Philip Kelley
Here's my take. With complex queries, there are always multiple solutions...
这是我的看法。对于复杂的查询，总是有多种解决方案......
First, get the latest date for every AccountID
首先，获取每个 AccountID 的最新日期
select AccountID, max(Date)
from Update2
group by AccountID
Stick this in a subquery, join back to the table to only get the "latest" entry
将其粘贴在子查询中，加入表以仅获取“最新”条目
select CInner.*, CMax.MaxDate
from Update2 CInner
inner join (-- Max date for every AccountID
select AccountID, max(Date) MaxDate
from Update2
group by AccountID) CMax
on CMax.AccountID = CInner.AccountID
and CMax.MaxDate = CInner.Date
Plug this back into the original query. Made it all a subquery to keep the Full Outer Join distinct (I also moved the inner joins together for comprehensibility)
将其插入到原始查询中。将其全部设为子查询以保持完全外连接不同（为了便于理解，我还将内连接移到了一起）
SELECT DISTINCT A.ID , C.Date
FROM IDTable A
INNER JOIN AccountTable B
ON B.AccountID = A.AccountID
INNER JOIN UserTable UT
ON UT.UserMnemonicID = A.AssignedTo
FULL OUTER JOIN Updates U
ON U.ID = A.ID
FULL OUTER JOIN (-- Update2, with latest date
select CInner.*, CMax.MaxDate
from Update2 CInner
inner join (-- Max date for every AccountID
select AccountID, max(Date) MaxDate
from Update2
group by AccountID) CMax
on CMax.AccountID = CInner.AccountID
and CMax.MaxDate = CInner.Date) C
ON C.AccountID = B.AccountID
WHERE A.StatusID NOT IN ('Complete','Open')
--Need help here to only return MAX date value:
AND C.Date=(SELECT MAX(C.Date) FROM Update2)
ORDER BY A.ID
This is as far as I can go. The problem here is that all JOINs will be resolved before the WHERE clause, so in the current form all those FULL OUTER JOINs are kind of pointless, as only those resulting rows with the max(Date) will be included.
这是我所能做到的。这里的问题是所有 JOIN 都将在 WHERE 子句之前解决，因此在当前形式中，所有这些 FULL OUTER JOIN 都是毫无意义的，因为只有那些具有 max(Date) 的结果行将被包括在内。
To mention, I find queries with full outer joins very difficult to get right. The fact that you have two in your query makes my eyes water, and moreso as they "branch off" of your core inner join queries. Are you definite that they need to be there?
值得一提的是，我发现使用全外连接的查询很难得到正确的结果。你的查询中有两个的事实让我的眼睛流泪，而且因为它们“分支”了你的核心内部连接查询。你确定他们需要在那里吗？
