如何在MySQL中不用DISTINCT查询仅完成一个项目且使用独有资源的建造商

算力阿峰

2026-7-17

建造商筛选解决方案

需求说明

需要从三张表（Builder、Project、Resources）中筛选出同时满足以下两个条件的建造商ID_BUILDER和NAME：

该建造商仅完成一个项目
该项目所使用的资源未被其他任何项目使用

示例数据

Builder表

ID_BUILDER	NAME
1	GustavEiffel
2	Egypcians
3	Me

Project表

ID_PROJECT	NAME	ID_BUILDER
1	EiffelTower	1
2	LionBelfort	1
3	Pyramids	2
4	PaperFlower	3

Resources表

ID_RESOURCE	NAME	ID_PROJECTS
1	Steel	1
2	Stone	2
2	Stone	3
3	Paper	4

解决方案SQL

基础关联查询写法

SELECT b.ID_BUILDER, b.NAME
FROM Builder b
JOIN Project p ON b.ID_BUILDER = p.ID_BUILDER
JOIN Resources r ON p.ID_PROJECT = r.ID_PROJECTS
WHERE 
    -- 筛选仅完成1个项目的建造商
    (SELECT COUNT(*) FROM Project WHERE ID_BUILDER = b.ID_BUILDER) = 1
    -- 筛选仅被1个项目使用的资源
    AND (SELECT COUNT(DISTINCT ID_PROJECTS) FROM Resources WHERE ID_RESOURCE = r.ID_RESOURCE) = 1
GROUP BY b.ID_BUILDER, b.NAME;

高效CTE写法（减少重复计算）

WITH BuilderProjectCount AS (
    SELECT ID_BUILDER, COUNT(ID_PROJECT) AS project_count
    FROM Project
    GROUP BY ID_BUILDER
    HAVING COUNT(ID_PROJECT) = 1
),
ResourceProjectCount AS (
    SELECT ID_RESOURCE, COUNT(DISTINCT ID_PROJECTS) AS project_usage_count
    FROM Resources
    GROUP BY ID_RESOURCE
    HAVING COUNT(DISTINCT ID_PROJECTS) = 1
)
SELECT b.ID_BUILDER, b.NAME
FROM Builder b
JOIN BuilderProjectCount bpc ON b.ID_BUILDER = bpc.ID_BUILDER
JOIN Project p ON b.ID_BUILDER = p.ID_BUILDER
JOIN Resources r ON p.ID_PROJECT = r.ID_PROJECTS
JOIN ResourceProjectCount rpc ON r.ID_RESOURCE = rpc.ID_RESOURCE;