Strives for the SQL statements-CodePudding

There are two TABLE, TB1, TB2
TB1:

 
The name value 
A1 10 
A2 15 
A3 12

TB2:

 
The name value 
A1 5 
A2 7 
A3 12 
A1 3

TB2 has represented by the value of TB1 data
Now I want to value what is left of the query TB1, if the name value=https://bbs.csdn.net/topics/0 won't show

CodePudding user response:

The SELECT a.n ame, Dr. Alue - c. j) diff_value FROM TB1 AS a
LEFT the JOIN (SELECT b.n ame, SUM (b.v alue) AS hj FROM TB2 AS GROUP b BY b.n ame) c
ON a.n ame=c.n ame WHERE (Dr. Alue - c. j) & gt; 0;

CodePudding user response:

reference 1st floor qq_18379499 response:

SELECT a.n ame, (Dr. Alue - c. j) diff_value FROM TB1 AS a
LEFT the JOIN (SELECT b.n ame, SUM (b.v alue) AS hj FROM TB2 AS GROUP b BY b.n ame) c
ON a.n ame=c.n ame WHERE (Dr. Alue - c. j) & gt; 0;

Ifnull (c. j, 0) it will be better

CodePudding user response:

The test data

 -DECLARE @ TABLE1 Table (Name varchar (50), the Value INT) 
INSERT INTO @ TABLE1 
SELECT 'A1, 10 UNION ALL 
15 UNION ALL SELECT 'A2' 
SELECT 'A3, 12 

DECLARE @ TABLE2 Table (Name varchar (50), the Value INT) 
INSERT INTO @ TABLE2 
5 UNION ALL SELECT 'A1', 
7 UNION ALL SELECT 'A2' 
UNION ALL SELECT A3, 12 
SELECT 'A1, 3 

Data, query the difference, filtering value=https://bbs.csdn.net/topics/0 
The SELECT A.N ame, Dr. Alue, Dr. ALUE2, DIF=Dr. Alue - Dr. ALUE2 FROM (
With table 1 after the correlation sum table 2, 
SELECT A. *, c. ALUE2 FROM @ TABLE1 A 
LEFT the JOIN (SELECT B.N ame, SUM (B.V alue) AS VALUE2 FROM @ TABLE2 B GROUP BY B.N ame) AS C - summation table 2 
ON C.N ame=A.N ame) A 
WHERE Dr. Alue - Dr. ALUE2 & lt;> 0