SQL Server Filter the record not same from what I have within the same result set?

xjreopfe 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(145)

So I have a result set, lets just assume that I joined multiple tables to pull the following result set.

And this is what I got
| ID | Column 2 | Column 3 |
| ------------ | ------------ | ------------ |
| 12300 | $80 | $70 |
| 12300 | $70 | $80 |
| 12300 | $70 | $80 |
| 12313 | $10 | $100 |
| 12313 | $50 | $70 |
| 12510 | $50 | $70 |
| 12510 | $70 | $50 |
| 12510 | $10 | $120 |

I hope this make sense, how can I take away those record which column 2 has duplicate value in column 3?

For example, for ID 12300, $80 can be found in column 3 within the same ID, so does $70.

But 12313 has 2 different value so I need to keep those both.

12510 $50 and $70 can be found in column 3 but $10 is not, so i only need the last record.

That being said, I wish my result look like this
| ID | Column 2 | Column 3 |
| ------------ | ------------ | ------------ |
| 12313 | $10 | $100 |
| 12313 | $50 | $70 |
| 12510 | $10 | $120 |

I wish I can bring up my query, but its way too much.

So if anyway could give me some solutions to solve this will be much appreciated!

My guess will be using NOT EXISTS.

Thank you so much!

sql-server

来源：https://stackoverflow.com/questions/75681581/filter-the-record-not-same-from-what-i-have-within-the-same-result-set

1条答案

按热度按时间

ifsvaxew1#

One approach is to use a left self join. Like this:

declare @demo table (id int, col2 int, col3 int);
INSERT INTO @demo VALUES
(12300  ,80 ,70),
(12300  ,70 ,80),
(12300  ,70 ,80),
(12313  ,10 ,100),
(12313  ,50 ,70),
(12510  ,50 ,70),
(12510  ,70 ,50),
(12510  ,10 ,120);

SELECT c2.* 
FROM @demo c2
LEFT JOIN @demo c3 ON c2.col2 = c3.col3 AND c2.id = c3.id
WHERE c3.col2 IS NULL;

The self join links col2 and col3 for the same id, and then only those records are returned where the left join fails (IS NULL).

BTW for future questions, it is always helpful, if you provide SQL to create a table and insert data (as I have done in my answer). People are much more willing to help, if they do not need to do so much typing themselves!

EDIT

If you want to use a NOT EXISTS then you construct it like this:

SELECT d.* 
FROM @demo d
WHERE NOT EXISTS 
    (SELECT 1 FROM @demo
     WHERE col3 = d.col2
     AND id = d.id);

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Filter the record not same from what I have within the same result set?

1条答案

相关问题

热门标签

最新问答