Dear Boss:
I have a query for the SQL statement to consult, I hope to be able to get results with a SQL statement, the problem is as follows:
Table x:
A B C D
------------------
BEIJING Guangzhou 1000 10
Beijing Shenzhen 1200 20
Beijing Shanghai 500 30
Beijing Tianjin 300 40
Beijing Henan 800 20
Table y:
A B
----------
Beijing Shenzhen
Beijing Shanghai
Table Z:
A B C D
------------------
BEIJING Guangzhou 200 10
BEIJING Shanghai-100-10
Beijing Sichuan 800 50
Objective: To remove the same keywords in table y from table x, plus the data in table Z, and to accumulate the same keywords (a,b as keywords)
The following results are obtained:
A B C D
------------------
Beijing Guangzhou 1200 20
Beijing Shanghai 400 20
Beijing Henan 800 20
Beijing Sichuan 800 50
Copy Code code as follows:
Select A,b,sum (c) as C,sum (d) as D from
(SELECT * FROM x UNION ALL SELECT * from Z) t
Where not exits (select 1 from y where y.a=t.a and y.b=t.b)
GROUP BY A,b