通过与同一记录的其他行进行比较来检索单行 [英] Retrieve single row by comparing with other rows for the same record

问题描述

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.

推荐答案

如果它总是最早的（按字母顺序）记录你想要的，你可以这样做：

If it's always the earliest (alphabetically) record that you want, you could do it like this:

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

嗨Thava

这只是一个建议。

如果你想要状态的优先级，你需要一个辅助表，你可以在那里将这些优先级设置为数字类型。

然后你可以将这个表与你的主表连接，为结果添加一个优先级列设置。

然后你可以再次分组选择MAX（优先级）（或MIN，具体取决于你如何设置优先顺序）。

这一切都是通过查询来完成的和子查询。

Hi Thava
This is just a suggestion.
If you want priorities for the Status, you'll need an auxiliary table where you can set those priorities as a numeric type.
Then you can join this table with your main table adding a priority column to the result set.
Then you can group select again selecting the MAX(priority) (or MIN depending on how you set the priority order).
All this is done with a query and subqueries.

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