oracle 如何在此示例中使用相交（可能是减号）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10800842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use intersect (and maybe minus) on this example
提问by sabisabi
lets say I have two tables:
假设我有两个表：
TableA
ID, Time, Size
0 5:00 600
1 8:00 800
2 7:00 100
3 1:50 140
4 2:40 300
12 3:40 300
TableB
ID, Time, Size
8 5:00 600
1 8:00 800
2 8:00 900
3 1:50 140
5 2:40 300
12 3:40 300
Now I would like to compare these two Tables: give all rows from Table A and B where the IDs are same (or lets say where the IDs are existing in both of the Tables) but lower then 12 - so that would kick out table A ID 0, 4, 12and from table B ID 8, 5, 12
现在我想比较这两个表：给出表 A 和 B 中 ID 相同的所有行（或者让我们说两个表中存在 ID 的位置）但低于 12 - 这样就会踢出表 AID 0, 4, 12从表 BID 8, 5, 12
After that I would have only 3 rows left. Now I would like to kick out all rows where there is one or more difference between the whole TableA row ID == TableB row ID
在那之后，我只剩下 3 行了。现在我想踢出整个 TableA 行 ID == TableB 行 ID 之间存在一个或多个差异的所有行
At the end there would be (if I see that correct) as output:
最后会有（如果我认为正确的话）作为输出：
ID, Time, Size
1 8:00 800
3 1:50 140
For this solution I need for sure intersect and maybe minus. At first I tought this SQL statement would do what I want:
对于这个解决方案，我肯定需要相交并且可能减去。起初我认为这个 SQL 语句会做我想要的：
select * from TableA where ID < 12 intersect select * from TableB where ID < 12
minus
select * from TableB where ID < 12;
But this is not working that well. Ist because of the intersect, Im using intersect for the whole row, I should use intersect for only the IDs and IF I have the intersect of the IDs (that would be 1,2,3) then I have to use ID TableA with ID 1,2,3minusTableB with ID 1,2,3. But how? Or do I miss something? Any ideas? Thank you
但这并不奏效。是因为相交，我对整行使用相交，我应该只对 ID 使用相交，如果我有 ID 的相交（即 1,2,3），那么我必须使用 IDTableA with ID 1,2,3减去TableB with ID 1,2,3。但是如何？或者我错过了什么？有任何想法吗？谢谢
回答by Lieven Keersmaekers
From your requirements and the testdata you've provided, you don't need intersect at all. A mere INNER JOINwould suffice.
根据您的要求和您提供的测试数据，您根本不需要相交。一个INNER JOIN就够了。
SELECT a.*
FROM TableA a
INNER JOIN TableB b ON b.ID = a.ID AND b.Time = a.Time AND b.Size = a.Size
回答by Romil Kumar Jain
select id from TableA where ID < 12 and id in (Select id from TableB)
union
select id from TableB where ID < 12 and id in (Select id from TableA)
or
或者
SELECT id,
time,
size
FROM (SELECT *
FROM tablea
WHERE id < 12
UNION ALL
SELECT *
FROM tableb
WHERE id < 12) a
GROUP BY id,
time,
size
HAVING Count(*) = 2
or
或者
SELECT *
FROM tablea A
WHERE EXISTS (SELECT 1
FROM tableb b
WHERE b.id = a.id
AND b.time = a.time
AND b.size = a.size)
