从众多中只选择一个 [英] Select only one from many
问题描述
我之前遇到过类似的问题,在问问问题的网站上搜索我自己的问题时,我只参与解决方案。
I这里有以下查询:
选择 fcontainer.id,FlaggId ,sum(FContainerRow.Units) as units,fcontainerplats.x,FContainerPlats.y,FContainerHylla.Name,
tempo.temponr,produkter.Artikelnummer,produkter。 Produktnamn
来自 fcontainer 内部 fcontainerrow on fcontainer.id = FContainerRow.FContainerId
inner join fcontainerplats on fcontainer.PlatsId = FContainerPlats.id
inner join FContainerHyll fcontainerplats.HyllId = FContainerHylla.Id
inner join tempo on fcontainerrow.ExtKeyId = tempo.lagernr
inner < span class =code-keyword> join produkter on produkter.Produktnr = tempo.produktnr
where ExtKeyName = ' Lager' 和 fcontainer.PlatsId> 0
group by fcontainer .id,flaggid,fcontainerplats.x,FContainerPlats.y,FContainerHylla.Name,tempo.temponr,produkter.Artikelnummer,produkter.Produktnamn
我的问题是,tempo中的多个条目在tempo.lagernr中可以具有相同的值,这意味着对于一个fcontainer.id,我得到多行。
我不关心我选择哪一行重复项,只要在我的最终查询中每个fcontainer.id只有一行。我一直在使用row_number()以及使用CTE,但是我无法正确使用它。
当使用rownumber计算行时我坚持选择rn = 1,只返回那些没有重复的行。
这应该有效。
WITH cte AS (
SELECT
fcontainer.id
,FlaggId
,Sum(FContainerRow.Units) AS 单位
,fcontainerplats.x
,FContainerPlats.y
,FContainerHylla.Name
,tempo.temponr
,produkter.Artikelnummer
,produkter.Produktnamn
, ROW_NUMBER() OVER ( PARTITION BY fcontainer.id)
FROM fcontainerrow
JOIN fcontainer ON FContainerRow.FContainerId = fcontainer.id
JOIN fcontainerplats ON fcontainer.PlatsId = FContainerPlats.id
JOIN FContainerHylla ON fcontainerplats.HyllId = FContainerHylla。 Id
JOIN Tempo ON fcontainerrow.ExtKeyId = tempo.lagernr
JOIN produkter ON produkter.Produktnr = tempo.produktnr
WHERE ExtKeyName = ' Lager'
AND fcontainer.PlatsId > 0
GROUP BY fcontainer .id
,flaggid
,fcontainerplats.x
,FContainerPlats.y
,FContainerHylla.Name
,tempo.temponr
,produkter.Artikelnummer
,produkter.Produktnamn
)
SELECT
fcontainer.id
,FlaggId
,units
,x
,y
,名称
,temponr
,Artikelnummer
,Produktnamn
FROM cte
WHERE rn = 1
I've had similar problems as this before and while scouring my own questions at ask-a-question sites I only come part to a solution.
I have the following query here:
select fcontainer.id, FlaggId, sum(FContainerRow.Units) as units ,fcontainerplats.x,FContainerPlats.y,FContainerHylla.Name,
tempo.temponr,produkter.Artikelnummer,produkter.Produktnamn
from fcontainer inner join fcontainerrow on fcontainer.id = FContainerRow.FContainerId
inner join fcontainerplats on fcontainer.PlatsId = FContainerPlats.id
inner join FContainerHylla on fcontainerplats.HyllId = FContainerHylla.Id
inner join tempo on fcontainerrow.ExtKeyId = tempo.lagernr
inner join produkter on produkter.Produktnr = tempo.produktnr
where ExtKeyName = 'Lager' and fcontainer.PlatsId > 0
group by fcontainer.id, flaggid, fcontainerplats.x,FContainerPlats.y, FContainerHylla.Name,tempo.temponr,produkter.Artikelnummer,produkter.Produktnamn
My problem is that multiple entries in tempo can have the same value in tempo.lagernr which means that for one fcontainer.id I get multiple rows.
I don't care which row of these duplicates I select as long as there is only one row per fcontainer.id in my final query. I've been tin kering with row_number() over partion as well as using CTE but I just cant get it right.
When "counting" the rows using rownumber I'm stuck at selecting rn = 1 which only returns those rows where there is no duplicate.
This should work.
WITH cte AS ( SELECT fcontainer.id ,FlaggId ,Sum(FContainerRow.Units) AS units ,fcontainerplats.x ,FContainerPlats.y ,FContainerHylla.Name ,tempo.temponr ,produkter.Artikelnummer ,produkter.Produktnamn ,ROW_NUMBER() OVER (PARTITION BY fcontainer.id) rn FROM fcontainerrow JOIN fcontainer ON FContainerRow.FContainerId = fcontainer.id JOIN fcontainerplats ON fcontainer.PlatsId = FContainerPlats.id JOIN FContainerHylla ON fcontainerplats.HyllId = FContainerHylla.Id JOIN Tempo ON fcontainerrow.ExtKeyId = tempo.lagernr JOIN produkter ON produkter.Produktnr = tempo.produktnr WHERE ExtKeyName = 'Lager' AND fcontainer.PlatsId > 0 GROUP BY fcontainer.id ,flaggid ,fcontainerplats.x ,FContainerPlats.y ,FContainerHylla.Name ,tempo.temponr ,produkter.Artikelnummer ,produkter.Produktnamn ) SELECT fcontainer.id ,FlaggId ,units ,x ,y ,Name ,temponr ,Artikelnummer ,Produktnamn FROM cte WHERE rn = 1
这篇关于从众多中只选择一个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!