There are two tables with about 10,000 rows, and we need to query the rows with differences. The current code is as follows:
SELECT number, version
FROM
(
SELECT a.number, b.version
FROM a
UNION ALL
SELECT b.number, b.version
FROM b
) tb
GROUP BY number, version
HAVING COUNT(*) = 1
ORDER BY numberBut here comes the problem. The above code can only query different rows, but it cannot display the rows in table a that are not in table b, and the rows in table b that are not in table a. Is there any way to display the rows in table a? 3 columns identified?
2
0
0
According to the original poster, the number and version in a single table will not be repeated. Create a composite index for the number and version of the two tables, and then execute the following sql
SELECT a.number, a.version,'from_a'
FROM a
where not exists (
SELECT 1 FROM b where a.number=b.number and a.version=b.version)
union all
SELECT b.number, b.version,'from_b'
FROM b
where not exists (
SELECT 1 FROM a where a.number=b.number and a.version=b.version)
ORDER BY number;
或者
SELECT a.number, a.version,'from_a'
from a left join b on a.number=b.number and a.version=b.version
where b.id is null
union all
SELECT b.number, b.version,'from_b'
from a right join b on a.number=b.number and a.version=b.version
where a.id is null
ORDER BY number;
下面這個效率可能會差點
Try itfull join ... where a is null or b is null. For example, using Postgres:
select
case when a.n is null then b.n else a.n end as n,
case when a.n is null then b.v else a.v end as v,
case when a.n is null then 'b' else 'a' end as src
from
(values(1, 2), (2, 3), (3, 4)) as a(n, v)
full join
(values(6, 7), (2, 3), (3, 9)) as b(n, v)
using (n, v)
where a.n is null or b.n is nullResult:
n | v | src
---+---+-----
1 | 2 | a
3 | 4 | a
3 | 9 | b
6 | 7 | b
(4 行記錄)
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
