如何通过OUTER JOIN统计各校区总实验室数及关联子网的实验室数

Alright, let's tackle this query to get the campus-level lab metrics you need. Based on the views you described (vw_Lab_Space for all labs, vw_Subnets linked via Lab_Space_Id), here's a clean, efficient SQL approach:

SQL Query #

SELECT
    ls.Campus_Name,
    COUNT(DISTINCT ls.Lab_Space_Id) AS Total_Laboratories,
    COUNT(DISTINCT CASE WHEN s.Subnet_Name IS NOT NULL THEN ls.Lab_Space_Id END) AS Labs_With_At_Least_One_Subnet
FROM vw_Lab_Space ls
LEFT JOIN vw_Subnets s 
    ON ls.Lab_Space_Id = s.Lab_Space_Id
GROUP BY ls.Campus_Name
ORDER BY ls.Campus_Name;

Breakdown of the Query #

• LEFT JOIN: This ensures we retain every lab from vw_Lab_Space, even those that don't have any associated subnets (which you mentioned are the majority).
• COUNT(DISTINCT ls.Lab_Space_Id): Calculates the total number of unique labs per campus. Using DISTINCT adds robustness—though you noted Lab_Space_Id is the primary key, it safeguards against any accidental duplicate entries in the view.
• COUNT(DISTINCT CASE ...): This counts only labs linked to at least one subnet. The CASE statement filters out labs with no matching subnet entries, and DISTINCT prevents overcounting labs that connect to multiple subnets (we only want to count each such lab once).

Alternative Approach (Correlated Subquery) #

If you prefer a more straightforward subquery style (easier to parse for some), this works too:

SELECT
    Campus_Name,
    COUNT(Lab_Space_Id) AS Total_Laboratories,
    (SELECT COUNT(DISTINCT Lab_Space_Id)
     FROM vw_Subnets s
     WHERE s.Lab_Space_Id = ls.Lab_Space_Id) AS Labs_With_At_Least_One_Subnet
FROM vw_Lab_Space ls
GROUP BY Campus_Name
ORDER BY Campus_Name;

Note: The first approach is generally more performant for large datasets, as it avoids running a subquery for each campus row.

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