选择每个产品类别的最佳结果
问题描述:
以下查询将回退以下数据。选择每个产品类别的最佳结果
我想只返回一个单一的结果每个产品编号,只有最orddate,而且相关的订单总额(ordttl)和单位成本(ucost)。
想法实施?感谢您的回复。
SELECT
ordln.pnum as 'Product Number',
prod.name as 'Product Name',
ordhd.snum,
sup.name,
ordhd.ordttl,
ORDHD.onum,
ORDHD.orddate,
ordln.ucost
FROM
scmdb.dbo.cksprodm prod (NOLOCK)
INNER JOIN scmdb.dbo.cksordln ordln (NOLOCK) on prod.pnum = ordln.pnum
INNER JOIN scmdb.dbo.cksordhd ordhd (NOLOCK) on ordhd.onum = ordln.onum
LEFT JOIN scmdb.dbo.ckssuplr sup (NOLOCK) on ordhd.snum = coalesce(sup.snum,
sup.asnum)
WHERE ordhd.orddate BETWEEN
DATEADD(month,-6,dateadd(day,datediff(day,0,getdate()),0) + '06:00') AND
GETDATE() -- order date is between 6 months ago and today.
AND ordln.pnum NOT IN (SELECT distinct PNUM
FROM scmdb.dbo.cksquohd qhd
inner join scmdb.dbo.cksquoln qln on qhd.quote = qln.quote
where qhd.qedate > GETDATE()
)
答
您可以使用ROW_NUMBER按日期进行排序,并获得最新的日期的一行:
SELECT *
FROM
(
SELECT
ordln.pnum as 'Product Number',
prod.name as 'Product Name',
ordhd.snum,
sup.name,
ordhd.ordttl,
ORDHD.onum,
ORDHD.orddate,
ordln.ucost,
row_number() over (partition by ordln.pnum order by ORDHD.orddate desc) as ranking
FROM
scmdb.dbo.cksprodm prod (NOLOCK)
INNER JOIN scmdb.dbo.cksordln ordln (NOLOCK) on prod.pnum = ordln.pnum
INNER JOIN scmdb.dbo.cksordhd ordhd (NOLOCK) on ordhd.onum = ordln.onum
LEFT JOIN scmdb.dbo.ckssuplr sup (NOLOCK) on ordhd.snum = coalesce(sup.snum,
sup.asnum)
WHERE ordhd.orddate BETWEEN
DATEADD(month,-6,dateadd(day,datediff(day,0,getdate()),0) + '06:00') AND
GETDATE() -- order date is between 6 months ago and today.
AND ordln.pnum NOT IN (SELECT distinct PNUM
FROM scmdb.dbo.cksquohd qhd
inner join scmdb.dbo.cksquoln qln on qhd.quote = qln.quote
where qhd.qedate > GETDATE()
)
) t
where ranking = 1
+0
非常简单的解决方案。谢谢。 –
无论是使用过一起ROW_NUMBER或交叉应用在上面1 – maSTAShuFu
还能有关系?如果是这样,他们应该如何处理? – HABO
@HABO由于orddate也包含时间,所以不太可能发生。如果他们真的发生了,任意选择其中一个结果就没问题。 –