英文:
Filter the record not same from what I have within the same result set?
问题
这是您所提供的结果集。您想要从中移除具有重复值的记录，即在"Column 2"中有与"Column 3"中相同值的记录。最终结果应如下所示：
| ID | Column 2 | Column 3 |
|---|---|---|
| 12313 | $10 | $100 |
| 12313 | $50 | $70 |
| 12510 | $10 | $120 |
您提到您的查询很复杂，但您的猜测是使用 "NOT EXISTS"。以下是一个示例 SQL 查询，可用于实现所需的结果：
SELECT ID, `Column 2`, `Column 3`
FROM your_table t1
WHERE NOT EXISTS (
SELECT 1
FROM your_table t2
WHERE t2.ID = t1.ID
AND t2.`Column 2` = t1.`Column 3`
)
请将 "your_table" 替换为您的实际表名。这个查询将返回符合您要求的结果集。
英文:
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!
答案1
得分: 1
一种方法是使用左自连接。像这样：
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;
这个自连接将col2和col3链接到相同的id，然后仅返回左连接失败的记录（IS NULL）。
顺便说一下，对于未来的问题，如果您提供SQL来创建表和插入数据（就像我在答案中所做的那样），那将非常有帮助。人们更愿意提供帮助，如果他们不需要自己键入太多内容！
编辑
如果您想使用NOT EXISTS，则构建它如下：
SELECT d.*
FROM @demo d
WHERE NOT EXISTS
(SELECT 1 FROM @demo
WHERE col3 = d.col2
AND id = d.id);
英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论