获取不同的列值行

问题描述

大家好，



我有一个包含列和值的表如下



stgID- -CMD --- KitProductID --- KitComponentID - KitQty created_On updated_On

14 ----- Insert-26532-00-0131--26532-00-0123--123 --- 2013 -05-01 18：50：50.447 2013-05-01 18：50：50.430

15 ----- UPDATE-26532-00-0131--26532-00-0123--123 --- 2013-05-01 18：51：50.447 2013-05-01 18：50：50.430

16 -----插入----- 26532-00-0131-- 26532-00-0123--122 --- 2013-05-01 18：52：50.447 2013-05-01 18：50：50.430

17 ----- UPDATE-26532-00 -0131--26532-00-0123--122 --- 2013-05-01 18：53：50.447 2013-05-01 18：50：50.430



在这些数据中，我有KitProductId，而ProductId有套件组件。所有Kitcomponents的ProductID都相同。这里我有同一个componentId的多个记录。我需要为每个组件只获得一条记录，即基于createdon的最新记录。



输出应该像



stgID - CMD --- KitProductID --- KitComponentID - KitQty created_On updated_On

15 ----- UPDATE-26532-00-0131--26532-00-0123-- 123 --- 2013-05-01 18:51:50.447 2013-05-01 18：50：50.430

17 ----- UPDATE-26532-00-0131--26532-00 -0123--122 --- 2013-05-01 18：53：50.447 2013-05-01 18：50：50.430



请帮我解决这个问题。我尝试了很多，但都没有帮助。



提前致谢。



Naveen。

Hello All,

I have one table with columns and values like below

stgID--CMD---KitProductID---KitComponentID--KitQty created_On updated_On
14-----Insert-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:50:50.447 2013-05-01 18:50:50.430
15-----UPDATE-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:51:50.447 2013-05-01 18:50:50.430
16-----Insert-----26532-00-0131--26532-00-0123--122 ---2013-05-01 18:52:50.447 2013-05-01 18:50:50.430
17-----UPDATE-26532-00-0131--26532-00-0123--122 ---2013-05-01 18:53:50.447 2013-05-01 18:50:50.430

In this data I have KitProductId and for that ProductId having kit components. ProductID is same for all the Kitcomponents. Here Iam having multiple records for same componentId . I need to get only one record for each componentid i.e latest record based on createdon.

Output should be like

stgID--CMD---KitProductID---KitComponentID--KitQty created_On updated_On
15-----UPDATE-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:51:50.447 2013-05-01 18:50:50.430
17-----UPDATE-26532-00-0131--26532-00-0123--122 ---2013-05-01 18:53:50.447 2013-05-01 18:50:50.430

Please help me on this. I have tried distinct and all but not helped.

Thanks in advance.

Naveen.

推荐答案

select * from
(
    select Row_Number() over(Partition by KitComponentID,Created_on order by KitComponentID,Created_On desc) as SrNo, *
    from tblnm
) as tmptbl
where SrNo=1

for sql 2000

for sql 2000

select *
from tblnm t1 where t1.stgID = (select top 1 stgid from tblnm t2 where t2.KitComponentID = t1.KitComponentID order by Created_On desc)

快乐编码！

：）

Happy Coding!
:)

这篇关于获取不同的列值行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！