选择两个日期之间的所有记录，但根据标牌选择同一天的一条记录

pb3skfrl 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(309)

我选择了查询：

select 
f.FirmaID,f.FirmaAdi,t.BelgeID,t.BelgeTuru,t.Tarih,t2.Plaka,t2.SasiNo,t4.AracMarka,t4.AracTip,case when x.Miktar=1 then 4 else x.miktar end as LastikAdet,
t3.CariKodu,t3.CariAdi,t3.CariGsm1,t3.CariGsm2,t3.CariTel1,t3.CariTel2,t3.CariAdres
from alsatr t WITH (NOLOCK)
left join Firma f WITH (NOLOCK) on f.FirmaID = t.AlsatrFirmaID 
left join AracBilgi t2 WITH (NOLOCK) on t2.AracBilgiUID = t.AsAracBilgiUID and t2.AracBilgiID= t.AracBilgi
left join Cari t3 WITH (NOLOCK) on t.AsCariUID= t3.CariUID
left join Araclar t4 WITH (NOLOCK) on t4.AracID= t2.AB_AracID
outer apply 
(select COUNT(1) soktak,Miktar FROM alsatD d WITH (NOLOCK) 
where 
d.AlsatDUID = t.AlsatrUID and d.AsStokKodu='LA-0001' group by Miktar) x 
where 
isnull(t3.FiloID,0) > 0 
and t.Tarih between '04.30.2020' and '04.31.2020'
and t.BelgeTuru=55
and x.soktak > 0
 and f.FirmaID not in (1,2,103,106,109,114)
order by t.Tarih desc, f.FirmaID desc, t.BelgeID desc

所以，我想选择两天之间的所有记录，但我想选择一个，最新的记录（可能取决于最后一次贝尔盖德）在同一天同一个板块（普拉卡）。

sql tsql

来源：https://stackoverflow.com/questions/62571577/select-all-records-between-two-dates-but-one-record-on-same-day-depending-on-pla

1条答案

按热度按时间

mitkmikd1#

将您的查询包含在 CTE 使用 ROW_NUMBER() 窗口功能：

WITH cte AS (
  <your query here>
)
SELECT 
  t.FirmaID, t.FirmaAdi, t.BelgeID, t.BelgeTuru, t.Tarih, t.Plaka, t.SasiNo, t.AracMarka, 
  t.AracTip, t.LastikAdet, t.CariKodu, t.CariAdi, t.CariGsm1, t.CariGsm2, t.CariTel1,
  t.CariTel2, t.CariAdres
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Tarih, Plaka ORDER BY BelgeID DESC) rn
  FROM cte
) t
WHERE t.rn = 1

赞(0）回复(0）举报 2021-07-24

我来回答

选择两个日期之间的所有记录，但根据标牌选择同一天的一条记录

1条答案

相关问题

热门标签

最新问答