book是认筹表，id是合同id，bargain_price是合同价格；
number是房间表，id是房间id，build_id是楼栋id；
book_room是合同房间关系表，room_id是房间id，contract_id是合同id
获取build_id=2对应的合同总数，和对应认筹合同的总价格
注意：一个认筹可以关联多个房间
写法1：JOIN（推荐，可直接利用优化器做 HASH JOIN mysql 8.0以上）
SELECT
COUNT(*) AS contract_count,
SUM(b.bargain_price) AS total_bargain_price
FROM (
SELECT DISTINCT br.contract_id
FROM number n
JOIN book_room br ON br.room_id = n.id
WHERE n.build_id = 2
) AS t
JOIN book b ON b.id = t.contract_id;
执行流程
1、内层派生表先执行
SELECT DISTINCT br.contract_id
FROM number n
JOIN book_room br ON br.room_id = n.id
WHERE n.build_id = 2;
-
先对
number做build_id = 2的过滤（走number(build_id)索引）； -
再把
number和book_room用room_id=id做INNER JOIN（一般走book_room(room_id)索引）； -
从匹配结果里提取
br.contract_id，去重（DISTINCT）。
结果： 得到所有关联到楼栋 2 的 唯一合同 ID 列表。
2、外层执行 JOIN
JOIN book b ON b.id = t.contract_id
-
数据库会把内层
t的去重结果当作小表（临时表或物化中间表）。 -
然后对
book表做等值 JOIN，b.id = t.contract_id。-
book.id必须是主键 → 索引查找非常快。
-
3、聚合
SELECT COUNT(*), SUM(b.bargain_price)
对 JOIN 后匹配成功的记录：
-
每条记录代表一份合同；
-
COUNT(*)统计合同数； -
SUM(b.bargain_price)累加合同价格。
4、输出结果
写法2：IN
SELECT
COUNT(*) AS contract_count,
SUM(b.bargain_price) AS total_bargain_price
FROM book b
WHERE b.id IN (
SELECT DISTINCT br.contract_id
FROM book_room br
JOIN number n ON br.room_id = n.id
WHERE n.build_id = 2
);
写法3：EXISTS
SELECT
COUNT(*) AS contract_count,
SUM(b.bargain_price) AS total_bargain_price
FROM book b
WHERE EXISTS (
SELECT 1
FROM book_room br
JOIN number n ON br.room_id = n.id
WHERE br.contract_id = b.id
AND n.build_id = 2
);
执行流程
1、先从 book 表扫描一行（或用索引快速定位），b 就是当前检查的合同。
2、对这条合同，去 book_room 表找是否存在至少一条 br.contract_id = b.id 且关联 number.build_id = 2 的记录：
SELECT 1
FROM book_room br
JOIN number n ON br.room_id = n.id
WHERE br.contract_id = b.id AND n.build_id = 2
LIMIT 1;
3、满足则保留，计算聚合
-
若
EXISTS返回TRUE，则这条book记录被保留用于：-
COUNT(*)计数 +1 -
SUM(b.bargain_price)累加
-
-
若
EXISTS返回FALSE，则该book记录被跳过。
4、扫描所有符合条件的 book 行后，输出聚合结果。
注： EXISTS 写法是从 外到内 执行（Nested Loop Semi Join）——即先枚举 book 表，再对每一条逐个跑子查询判断是否存在匹配。