请帮助我搜索产品的新价格(通过sql命令) [英] Please help me to search new prices of products (by sql command)

问题描述

亲爱的所有人，

我有一张桌子，例如:

Dear all,

I have a table such as:

Product_Code     UnitID     Price        Modify
=====================================================
A                CAI        3.000        22/02/2012          -> New
A                CAI        2.000        21/02/2012
A                CAI        1.000        20/02/2012

A                HOP        6.000        22/02/2012          -> New
A                HOP        5.000        21/02/2012

请帮助我在下面选择结果

Please help me to select result below

Product_Code	UnitID	Price	Modify
=====================================================
A                CAI        3.000        22/02/2012
A                HOP        6.000        22/02/2012

请帮助我搜索产品的新价格
-已删除电子邮件-

谢谢，
西贡

[edit]已添加代码块-OriginalGriff [/edit]

Please help me to search new price for products
--EMAIL REMOVED--

Thanks,
SaiDon

[edit]Code blocks added - OriginalGriff[/edit]

推荐答案

我还没有尝试过，但是告诉我您得到了什么:

I have not Try this but tell me what u getting:

select * from produce a
where modify = (select max(Modify) from product where productid=a.productid)

如果修改"列为datetime，则此方法应该有效

This should work if Modify column is datetime

select  a.Product_Code,
        a.UnitID,
        a.Price,
        a.Modify
from    dbo.Products as a
join    (
        select  Product_Code,
                UnitID,
                max(Modify) as Modify
        from    dbo.Products
        group
        by      Product_Code,
                UnitID
        ) as b
        on b.Product_Code = a.Product_Code and b.UnitID = a.UnitID and b.Modify = a.Modify

尝试:

SELECT * FROM (
  SELECT Price, UnitId, [Modify],
    RANK() OVER (PARTITION BY UnitId ORDER BY [Modify] DESC) dest_rank
    FROM Products
 ) p WHERE dest_rank=1

这篇关于请帮助我搜索产品的新价格(通过sql命令)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！