有兩個TABLE,TB1,TB2
TB1:
name value
a1 10
a2 15
a3 12
TB2:
name value
a1 5
a2 7
a3 12
a1 3
TB2表示已經用了TB1的value的資料
現在我想查詢TB1的 value還剩下多少,如果name的value=https://bbs.csdn.net/topics/0 就不顯示了
uj5u.com熱心網友回復：
SELECT a.name,(a.value-c.hj) diff_value FROM TB1 AS aLEFT JOIN (SELECT b.name,SUM(b.value) AS hj FROM TB2 AS b GROUP BY b.name) c
ON a.name = c.name WHERE (a.value-c.hj) > 0;
uj5u.com熱心網友回復：
ifnull(c.hj,0)這樣子會好一點
uj5u.com熱心網友回復：
--測驗資料
DECLARE @TABLE1 Table(Name varchar(50),Value INT)
INSERT INTO @TABLE1
SELECT 'A1',10 UNION ALL
SELECT 'A2',15 UNION ALL
SELECT 'A3',12
DECLARE @TABLE2 Table(Name varchar(50),Value INT)
INSERT INTO @TABLE2
SELECT 'A1',5 UNION ALL
SELECT 'A2',7 UNION ALL
SELECT 'A3',12 UNION ALL
SELECT 'A1',3
--查詢差額，過濾value=https://bbs.csdn.net/topics/0的資料
SELECT A.Name,A.Value,A.VALUE2,DIF=A.Value-A.VALUE2 FROM (
--用表1關聯求和后的表2
SELECT A.*,C.VALUE2 FROM @TABLE1 A
LEFT JOIN (SELECT B.Name,SUM(B.Value) AS VALUE2 FROM @TABLE2 B GROUP BY B.Name) AS C --求和表2
ON C.Name=A.Name) A
WHERE A.Value-A.VALUE2<>0
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8743.html
標籤：MySQL
上一篇：mysql如何隔1000條取一條