SQL每日一题【0305】

每日一题

有如下表T0305a ,T0305b
T0305a
+------+--------+
| ID   | ZHI | 
+------+--------+
|    1 | 1      | 
|    1 | 2      | 
|    1 | 1      |   
|    2 | 2      |   
|    2 | 1      |   
+------+--------+
T0305b
+------+--------+
| ID   | ZHI | 
+------+--------+
|    1 | 10     | 
|    2 | 10      |   
+------+--------+
从T0305B表里核减表T0305A，要求T0305B.ID=T0305A.ID,结果如下:

CREATE TABLE T0305A(
ID INT,
ZHI INT
)
INSERT INTO T0305A VALUES (1,1);
INSERT INTO T0305A VALUES (1,2);
INSERT INTO T0305A VALUES (1,1);
INSERT INTO T0305A VALUES (2,2);
INSERT INTO T0305A VALUES (2,1);


CREATE TABLE T0305B(
ID INT,
ZHI INT
)
INSERT INTO T0305B VALUES (1,10);
INSERT INTO T0305B VALUES (2,10);

解法

• 1

SELECT
	b.ID,
	b.ZHI - a.ZHI AS ZHI 
FROM
	t0305b b
	INNER JOIN ( SELECT ID, sum( ZHI ) AS ZHI FROM t0305a GROUP BY ID ) a ON b.ID = a.ID;

• 2

SELECT
	b.id,
	b.zhi - a.zhi AS zhi 
FROM
	t0305b b,
	( SELECT id, sum( zhi ) AS zhi FROM t0305a GROUP BY id ) a 
WHERE
	b.id = a.id;