如何在关联三张表的SQL查询中按电缆类型统计总数

Got it, let's fix this up for your report. You're looking to tally up counts grouped by the buss_cable field from your three joined tables—here's how to adjust your SQL query to get that done.

Basic Total Count by Cable Type #

If you just need a clean breakdown of each cable type and how many times it appears, use this:

SELECT 
    bd.buss_cable,
    COUNT(*) AS total_count
FROM Residence AS r
LEFT JOIN buss_drops AS bd ON r.residence_id = bd.Residence_residence_id
LEFT JOIN bus_sequience AS bs ON bd.buss_id = bs.bus_buss_id
-- Paste your full WHERE clause here (e.g., WHERE r.city = 'Chatsworth')
GROUP BY bd.buss_cable;

• GROUP BY bd.buss_cable clusters all rows with the same cable type together
• COUNT(*) counts every row in each cluster to give you the total for that cable type

Handling Duplicates (If Needed) #

If your joins are creating duplicate rows (like one buss drop linked to multiple sequence entries), use COUNT(DISTINCT bd.buss_id) instead to count unique drops per cable type:

SELECT 
    bd.buss_cable,
    COUNT(DISTINCT bd.buss_id) AS unique_drop_count
FROM Residence AS r
LEFT JOIN buss_drops AS bd ON r.residence_id = bd.Residence_residence_id
LEFT JOIN bus_sequience AS bs ON bd.buss_id = bs.bus_buss_id
-- Add your WHERE conditions here
GROUP BY bd.buss_cable;

Including Additional Context #

If you want to keep some related details from the other tables alongside the totals, use aggregate functions like GROUP_CONCAT to list related IDs:

SELECT 
    bd.buss_cable,
    COUNT(*) AS total_count,
    GROUP_CONCAT(DISTINCT r.residence_id SEPARATOR ', ') AS linked_residences,
    GROUP_CONCAT(DISTINCT bs.Drop_id SEPARATOR ', ') AS linked_drops
FROM Residence AS r
LEFT JOIN buss_drops AS bd ON r.residence_id = bd.Residence_residence_id
LEFT JOIN bus_sequience AS bs ON bd.buss_id = bs.bus_buss_id
-- Your WHERE clause goes here
GROUP BY bd.buss_cable;

Just remember to fill in the rest of your WHERE clause before the GROUP BY statement, and adjust the aggregate functions based on exactly what details you need in your report.

内容的提问来源于stack exchange，提问作者Pete Watters Chatsworth