我有一個現有的查詢，它正在為該特定專案的每條記錄獲取每個下面的 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
任何幫助，將不勝感激！
謝謝。
uj5u.com熱心網友回復：
獲取具有兩個欄位最大值的記錄的腳本如下
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
uj5u.com熱心網友回復：
也許您需要子查詢來查找每個專案的最大日期。在我的情況下，即 ct_max_date 之后，您通過 max_date 和 item_Id 加入該子查詢
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
inner join (
select max(exdt) as max_date, ct.cpc
from dbo.ct
group by ct.dt
) as ct_max_date on a.cpc=ct_max_date.cpc and a.exdt=ct_max_date.max_date
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/344763.html
上一篇：如何在查詢中添加輔助列？