基于等值或求和匹配两表对应行的SQL查询问题

原表结构 #

Table1
(Id int, 
AName varchar(100),
AValue DECIMAL(18,2), 
BName varchar(100),
BValue DECIMAL(18,2),
Type VARCHAR(1)
)

其中Type字段值仅为'A'或'B'：当Type='A'时，仅Id、AName、AValue字段有有效值；当Type='B'时，仅Id、BName、BValue字段有有效值。

拆分后的表结构及测试数据 #

已将原表拆分为仅包含Type='A'数据的TableA和仅包含Type='B'数据的TableB，建表及插入数据的SQL如下：

CREATE TABLE TableA 
 (Id INT NOT NULL,
 AName VARCHAR(200) NOT NULL,
 AValue DECIMAL(18,2) NOT NULL,
 BName VARCHAR(200), 
 BValue DECIMAL(18,2),
 Type VARCHAR(1) NOT NULL
 );

 CREATE TABLE TableB 
 (Id INT NOT NULL,
 AName VARCHAR(200),
 AValue DECIMAL(18,2),
 BName VARCHAR(200) NOT NULL, 
 BValue DECIMAL(18,2) NOT NULL,
 Type VARCHAR(1) NOT NULL
 );


INSERT INTO TableA VALUES (1,'A',100,null, null,'A');
INSERT INTO TableA VALUES (1,'B',15,null, null,'A');
INSERT INTO TableA VALUES (1,'C',13,null, null,'A');
INSERT INTO TableA VALUES (1,'D',15,null, null,'A');
INSERT INTO TableA VALUES (2,'C',2,null, null,'A');
INSERT INTO TableA VALUES (2,'C',2,null, null,'A');


INSERT INTO TableB VALUES (1,null, null,'D',50,'B');
INSERT INTO TableB VALUES (1,null, null,'E',50,'B');
INSERT INTO TableB VALUES (1,null, null,'F',13,'B');
INSERT INTO TableB VALUES (1,null, null,'G',15,'B');
INSERT INTO TableB VALUES (1,null, null,'H',15,'B');
INSERT INTO TableB VALUES (2,null, null,'M', 4,'B');

查询需求 #

找出满足以下任一条件的对应AName与BName，输出格式为(Id, AName,AValue, BName, BValue)：

• AValue = BValue
• AValue为同一Id下多个BValue的总和
• BValue为同一Id下多个AValue的总和

尝试的SQL及问题 #

尝试了以下关联查询SQL，但未得到预期结果：

SELECT a.Id, a.AName, a.AValue, b.BName, b.BValue
FROM TableA a
JOIN TableB b ON a.Id = b.Id
WHERE (a.AValue = b.BValue)
    OR (a.AValue = (SELECT SUM(BValue) FROM TableB WHERE Id = b.Id));

预期输出 #

(1, 'A', 100, 'D', 50)
(1, 'A', 100, 'E', 50)
(1, 'B', 15, 'G', 15)
(1, 'C', 13, 'F', 13)
(1, 'D', 15, 'H', 15)
(2, 'C', 2, 'M', 4)
(2, 'C', 2, 'M', 4)

注：原预期输出中A的AValue写为50应为笔误，实际对应数据为100。

正确的SQL方案 #

我们需要分三个条件分别处理，再通过UNION ALL合并结果，完整SQL如下：

-- 条件1：AValue与BValue直接相等
SELECT a.Id, a.AName, a.AValue, b.BName, b.BValue
FROM TableA a
JOIN TableB b ON a.Id = b.Id
WHERE a.AValue = b.BValue

UNION ALL

-- 条件2：AValue等于同一Id下多个BValue的总和
SELECT a.Id, a.AName, a.AValue, b.BName, b.BValue
FROM TableA a
JOIN TableB b ON a.Id = b.Id
WHERE EXISTS (
    SELECT 1
    FROM TableB b2
    WHERE b2.Id = a.Id
    GROUP BY b2.BValue
    HAVING SUM(b2.BValue) = a.AValue
)
AND b.BValue = (
    SELECT b2.BValue 
    FROM TableB b2 
    WHERE b2.Id = a.Id 
    GROUP BY b2.BValue 
    HAVING SUM(b2.BValue) = a.AValue
)

UNION ALL

-- 条件3：BValue等于同一Id下多个AValue的总和
SELECT a.Id, a.AName, a.AValue, b.BName, b.BValue
FROM TableA a
JOIN TableB b ON a.Id = b.Id
WHERE b.BValue = (SELECT SUM(AValue) FROM TableA a2 WHERE a2.Id = a.Id);

说明 #

1. 条件1直接关联TableA和TableB，匹配数值相等的记录；
2. 条件2通过子查询筛选出AValue等于同一Id下某组重复BValue总和的记录，并关联对应B记录；
3. 条件3通过子查询计算同一Id下所有AValue的总和，匹配等于该总和的BValue记录；
4. 使用UNION ALL而非UNION，保留重复的匹配记录（如Id=2的两条A记录关联同一条B记录的情况）。

内容的提问来源于stack exchange，提问作者marry_anne