通过与同一记录的其他行进行比较来检索单行 | 通过与同一记录的其他行进行比较来检索单行

本文介绍了通过与同一记录的其他行进行比较来检索单行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

I've a table with data. The rows are repeated but the change is in only one column(status). Let the status column data be (A, B, C).
Sometimes data will be available with status A, B and C . Then 3 rows will be created for one record(ID=2001). What I need is, If A,B,C are available, the query should return only the row where Status=' A'. Sometimes for another record (ID=2005) B &C will be status. Then I should get row Where Status='B'

How can i tackle this situation with SQLQuery? I'm using SQLServer2008. Any responses will be appreciated.

推荐答案

select ID, min(Status) as Status 
from TABLENAME
group by ID
order by ID

SELECT * 
FROM (
       SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [STATUS]) AS RNO, * 
       FROM  <yourtable> /*where ID =2001 AND[STATUS] IN ('A','B','C')*/
     ) X
WHERE RNO =1
</yourtable>

希望这也会产生你想要的东西

Hope this will also produce what you wnat

这篇关于通过与同一记录的其他行进行比较来检索单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！