4表连接计数查询

Question

我使用的SQL Server数据库有4个表，如下所示：

产品表：Product_id是表的主键：

| Product_id | Product_name | Description |

项目表：Project_id是表的主键，Product_id是Product表的外键。

| Project_id | Project_name | Project_start_date | Product_id |

Participant表：Participant_id是表的主键，Project_id是Project表的外键。

| Participant_id | Participant_name | Participant_email | Project_ID |

响应表：Response_id是表的主键，participant_id是Participant表的外键。

我的问题是，我是否可以将所有4个表连接在一起，列出产品名称、项目名称、参与者电子邮件，并获得每个项目的参与者数量和每个项目的所有回复？

编辑：我觉得最好把代码放在这里。这是我尝试过的，所以请不要告诉我只使用JOIN。。。

这是我得到的代码总参与者：

SELECT
projects.Project_ID,
count(*) total_Participants,
projects.Project_Name,
projects.Product_ID,
products.Product_Name
FROM
Project projects
INNER JOIN Participant participant on
projects.Project_ID = participant.Project_ID
INNER JOIN Products products ON
products.Product_ID = projects.Product_ID
group by
projects.Project_ID,
projects.Project_Name,
projects.Product_ID,
products.Product_Name,
projects.Project_ID
ORDER BY
projects.Project_ID DESC

这是我得到的全部响应代码：

SELECT
projects.Project_ID,
count(*) total_Participants,
projects.Project_Name,
projects.Product_ID,
products.Product_Name
FROM
Project projects
INNER JOIN Participant participant on
projects.Project_ID = participant.Project_ID
INNER JOIN Products products ON
products.Product_ID = projects.Product_ID
INNER JOIN Response Response ON
Response.Participant_ID = participant.Participant_ID
group by
projects.Project_ID,
projects.Project_Name,
projects.Product_ID,
products.Product_Name,
projects.Project_ID
ORDER BY
projects.Project_ID DESC

这是我可以使用一个查询语句来获得participant_email、总参与者和总响应的一种方式吗？

Answer 1

如果没有一个预期结果的例子，这是我能想到的最好的结果。

一些样本数据：

declare @product table
(
product_id int,
product_name nvarchar(20)
);
insert into @product (product_id, product_name) values
(1, 'Product ABC'),
(2, 'Product DEF'),
(3, 'Product GHI');

declare @project table
(
project_id int,
project_name nvarchar(20),
product_id int
);
insert into @project (project_id, project_name, product_id) values
(1, 'Project 001', 1),
(2, 'Project 002', 1),
(3, 'Project 003', 2),
(4, 'Project 004', 3);

declare @participant table
(
participant_id int,
participant_name nvarchar(20),
participant_email nvarchar(20),
project_id int
);
insert into @participant (participant_id, participant_name, participant_email, project_id) values
(1, 'Andy', '[e-mail]', 1),
(2, 'Beatrice', '[e-mail]', 1),
(3, 'Charles', '[e-mail]', 2),
(4, 'Charles', '[e-mail]', 4),
(5, 'David', '[e-mail]', 3),
(6, 'Eve', '[e-mail]', 2);

declare @response table
(
response_id int,
response_content nvarchar(100),
participant_id int
);
insert into @response (response_id, response_content, participant_id) values
(1, 'please', 1),
(2, 'provide', 2),
(3, 'sample', 2),
(4, 'data', 3),
(5, 'next', 5),
(6, 'time', 4),
(7, 'thank', 6),
(8, 'you', 6),
(9, '>.<', 6);

可能的解决方案，使用通用表表达式(CTE(隔离计数子查询：

with cte_part as
(
select proj.project_id, count(1) as 'participant_count'
from @project proj
join @participant part on part.project_id = proj.project_id
group by proj.project_id
),
cte_resp as
(
select proj.project_id, count(1) as 'response_count'
from @project proj
join @participant part on part.project_id = proj.project_id
join @response resp on resp.participant_id = part.participant_id
group by proj.project_id
)
select  prod.product_name,
proj.project_name,
part.participant_email,
cp.participant_count,
cr.response_count
from @product prod
join @project proj on proj.product_id = prod.product_id
join @participant part on part.project_id = proj.project_id
join cte_part cp on cp.project_id = proj.project_id
join cte_resp cr on cr.project_id = proj.project_id
order by prod.product_name;

这给了我：

product_name         project_name         participant_email    participant_count response_count
-------------------- -------------------- -------------------- ----------------- --------------
Product ABC          Project 001          [e-mail]        2                 3
Product ABC          Project 001          [e-mail]    2                 3
Product ABC          Project 002          [e-mail]     2                 4
Product ABC          Project 002          [e-mail]         2                 4
Product DEF          Project 003          [e-mail]       1                 1
Product GHI          Project 004          [e-mail]     1                 1