sql-server - SQL选择第二列最大的唯一列
问题描述
请问如何结合最大和不同的总是唯一的ID和该唯一ID的列的最大值?
例如,如果我有,
OrdinaceId Priloha2Id
5 1
5 2
6 2
6 4
7 1
结果将是
OrdinaceId Priloha2Id
5 2
6 4
7 1
如何仅返回 1 行(具有相同的 Id)最多 Priloha2Id?
select * from (select
distinct ord.Id as OrdinaceId,
ord.CleneniPzsId,
posk.Id,
posk.ICO as Ico,
zar.ICZ as Icz,
posk.NazevZkraceny as Zkratka,
case when cle.Primariat = 0 then cle.Icp end as Icp,
IIF(cle.Primariat = 1, pri.OborKod , cle.OdbornostKod) as OdbornostKod,
ord.Nazev as OrdinaceNazev,
(select Street + N' ' + DescriptiveNo + N', ' + PostCode + N' ' + City from ICIS_Repl.repl.Address where Code = ord.NavAddressCode and Type = N'ORDINACE' and (ValidFrom is null or ValidFrom <= GETDATE()) and (ValidTill is null or ValidTill >= GETDATE() or ValidTill = N'1753-01-01 00:00:00')) as OrdinaceAdresaCela,
pril.Id as Priloha2Id,
cle.Smluvni,
cisP2KomunikaceStav.Nazev as EP2,
cisPzs.Nazev as StavPzp,
pril.Status,
pril.Info,
pril.PriPlatnostOd as PlatnostOd,
pril.PriPlatnostDo as PlatnostDo
from Ordinace ord
left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
left join ZarizeniPZS zar on cle.ZarizeniPzsId = zar.Id
left join PoskytovatelZS posk on zar.PoskytovatelZsId = posk.Id
left join Primariat pri on cle.PrimariatId = pri.Id
left join Pril2Formular pr on pr.CleneniPzsId = cle.Id
left join Priloha2 pril on pril.Id = pr.Priloha2Id and (pril.PriPlatnostOd <= GETDATE() or pril.PriPlatnostOd is null) and (pril.PriPlatnostDo is null or pril.PriPlatnostDo >= GETDATE())
join CisCiselnik cisP2KomunikaceStav on posk.P2KomunikaceStavKod = cisP2KomunikaceStav.Kod AND cisP2KomunikaceStav.Oblast = N'P2KomunikaceStav' and cisP2KomunikaceStav.PlatnostOd <= GETDATE() and (cisP2KomunikaceStav.PlatnostDo is null or cisP2KomunikaceStav.PlatnostDo >= GETDATE())
left join P2Formular form on form.Icp = cle.Icp and form.Aktivni = 1
left join CisCiselnik cisPzs on form.P2StavPzpKod = cisPzs.Kod AND cisPzs.Oblast = N'P2StavPZP' and cisPzs.PlatnostOd <= GETDATE() and (cisPzs.PlatnostDo is null or cisPzs.PlatnostDo >= GETDATE())
left join P2ZarizeniPZS z on form.P2ZarizeniPzsId = z.Id and z.Icz = zar.ICZ and z.PoskytovatelZsId = posk.Id
join Smlouva smlv on smlv.Id = pril.SmlouvaId
left join SmluvniVykon smlVyk on smlVyk.CleneniPzsId = cle.Id and smlVyk.PlatnostOd <= GETDATE() and (smlVyk.PlatnostDo is null or smlVyk.PlatnostDo >= GETDATE())
left join SmlVykonVyjadreniRL vyjadreni on vyjadreni.Id = smlVyk.VyjadreniRLId ) a
解决方案
按 Id 从表组中选择 Id, max(Priloha2Id)
推荐阅读
- kendo-ui - 升级到 Kendo 2020.1.219 后,日历和时间选择器未在 Kendo Grid 中打开
- pine-script - 仅当 PnL 为正时金字塔
- python - 如何使用 ssl 启动 flask_socketio 应用程序?
- python - 深度学习中的 StackEnsemble
- kubernetes - 无法使用 initContainers 访问卷内容
- jmeter - 如何从命令行禁用 jMeter 中的配置元素
- c++ - 没有参数的构造函数给我错误
- xamarin.forms - 如何在组列表视图中使用分页
- java - Is there a way to crop an SVG using java-batik?
- spring - Spring boot static resources and controllers endpoint mixed