sql 服务器 - 一个 SQL 查询，用于从另一个表中引用的不同表中获取项目数

Question

我有 5 个表，它们是

- Project(PK Project_ID), 
- Bottles (PK Bottle_ID, FK Project_ID), 
- Plastics (PK Plastic_ID, FK Project_ID), 
- Glasses (PK Glass_ID,FK Project_ID) and 
- Cups (PK Cup_ID, FK Project_ID)

如何获取一个SQL查询来返回每个项目的

Answer 1

select p.project_id, count(distinct bottle_id), count(distinct plastic_id), count(distinct glass_id), count(distinct cup_id)
from project p
left join bottles b on p.project_id = b.project_id
left join plastics pl on p.project_id = pl.project_id
left join glasses g on p.project_id = g.project_id
left join cups c on p.project_id = c.project_id
group by p.project_id

Answer 2

Create table Project (Project_ID int )
Create table Bottles ( Bottle_ID int,  Project_ID int)
Create table Plastics ( Plastics_ID int,  Project_ID int)
Create table Glasses ( Glasses_ID int,  Project_ID int)
Create table Cups ( Cups_ID int,  Project_ID int)
insert into Project (Project_ID) values (1)
insert into Bottles (Bottle_ID,Project_ID) values (1,1)
insert into Bottles (Bottle_ID,Project_ID) values (2,1)
insert into Bottles (Bottle_ID,Project_ID) values (3,1)
insert into Plastics (Plastics_ID,Project_ID) values (1,1)
insert into Plastics (Plastics_ID,Project_ID) values (2,1)
insert into Glasses (Glasses_ID,Project_ID) values (1,1)
insert into Glasses (Glasses_ID,Project_ID) values (2,1)
insert into Cups (Cups_ID,Project_ID) values (1,1)
insert into Cups (Cups_ID,Project_ID) values (2,1)
select distinct p.Project_ID, 
(select COUNT(*) from Bottles where p.Project_ID=Project_ID) Bottles ,
(select COUNT(*) from Plastics where p.Project_ID=Project_ID) Plastics ,
(select COUNT(*) from Glasses where p.Project_ID=Project_ID) Glasses ,
(select COUNT(*) from Cups where p.Project_ID=Project_ID) Cups 
from Project p

Answer 3

SELECT P.Project_ID, COUNT(B.BOTTLE_ID) AS BOTTLECOUNT,
COUNT(L.PLASTIC_ID) AS PLASTICCOUNT,
COUNT(G.GLASSES_ID) AS GLASSCOUNT,
COUNT(C.CUP_ID) AS CUPCOUNT
FROM PROJECT AS P
INNER JOIN BOTTLES B
ON P.PROJECT_ID=B.PROJECT_ID
INNER JOIN PLASTIC L
ON P.PROJECT_ID=L.PROJECT_ID
INNER JOIN GLASSES G
ON P.PROJECT_ID = G.PROJECT_ID
INNER JOIN CUPS C
ON P.PROJECT_ID=C.PROJECT_ID
GROUP BY P.PROJECT_ID, BOTTLE_ID, PLASTIC_ID, GLASSES_ID, CUP_ID
ORDER BY PROJECT_ID

Answer 4

你的意思是你希望显示唯一的项目 ID，至少有>=1 瓶、>=1 个塑料、>=1 个

玻璃杯、>=1 个杯子？如果是这样，您可以编写这样的查询，该查询基于FK（ProjectID）连接所有表并按ProjectID分组：

SELECT p.ProjectID
    ,COUNT(DISTINCT (b.Bottle_ID)) 'CountBottles'
    ,COUNT(DISTINCT (p.Plastic_ID)) 'CountPlastic'
    ,COUNT(DISTINCT (g.Glass_ID)) 'CountGlass'
    ,COUNT(DISTINCT (c.Cup_ID)) 'CountCup'
FROM Project p
INNER JOIN Bottles b ON p.projectID = b.projectID
INNER JOIN Plastics pl ON pl.projectID = p.projectID
INNER JOIN Glasses g ON g.projectID = p.projectID
INNER JOIN Cups c ON c.projectID = p.projectID
GROUP BY p.ProjectID