sql - SQL：向现有 MAX 日期查询添加另一个 MAX 字段

Question

我有一个现有查询，该查询正在为该特定项目的每条记录获取以下 exdt 字段的最大日期。我正在尝试向 sqn 字段添加一个额外的 MAX 计算，本质上是同时选择具有最高 exdt 和 sqn 字段的记录。我尝试在 WHERE 中添加一个额外的 "AND (a.sqn = (SELECT MAX(sqn) ，它似乎不喜欢多选。

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn

FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc

WHERE (a.exdt = (SELECT MAX(exdt) AS exdt FROM  dbo.ct AS a 

任何帮助，将不胜感激！

谢谢。

标签： sqlmax

Answer 1

获取具有两个字段最大值的记录的脚本如下

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn
FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc
WHERE 
a.exdt = (SELECT MAX(exdt) AS exdt FROM  dbo.ct )
and 
a.sqn= (SELECT MAX(sqn) AS exdt FROM  dbo.ct)  

您还可以按如下方式使用CTE

with 
cte_principal as
(SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn
 FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc),
cte_max_exdt as (SELECT MAX(exdt) AS exdt FROM  dbo.ct),
cte_max_sqn as (SELECT MAX(sqn) AS exdt FROM  dbo.ct)

select p.* from cte_principal p  
inner join cte_max_exdt  on p.exdt =cte_max_exdt.exdt
inner join cte_max_sqn  pn p.sqn=cte_max_sqn.sqn