我有幾個表格，如下所示：
公司：
| 公司代碼 | 首席執行官 |
|---|---|
| “C1” | “約翰” |
| “C2” | “安德魯” |
帶領：
| 鉛代碼 | 公司代碼 |
|---|---|
| “LM1” | “C1” |
| “LM2” | “C2” |
高級的：
| 鉛代碼 | 公司代碼 | Senior_code |
|---|---|---|
| “LM1” | “C1” | “SM1” |
| “LM1” | “C1” | “SM2” |
| “LM2” | “C2” | “SM3” |
經理：
| 鉛代碼 | 公司代碼 | Senior_code | 經理代碼 |
|---|---|---|---|
| “LM1” | “C1” | “SM1” | “M1” |
| “LM2” | “C2” | “SM3” | “M2” |
| “LM2” | “C2” | “SM3” | “M3” |
員工：
| 鉛代碼 | 公司代碼 | Senior_code | 經理代碼 | Employee_Code |
|---|---|---|---|---|
| “LM1” | “C1” | “SM1” | “M1” | “E1” |
| “LM1” | “C1” | “SM1” | “M1” | “E2” |
| “LM2” | “C2” | “SM3” | “M2” | “E3” |
| “LM2” | “C2” | “SM3” | “M3” | “E4” |
我想列印出按公司代碼分組的 CEO、潛在客戶總數、高管、經理和員工。我的輸出應該是這樣的：
輸出/輸出：
C1 約翰 1 2 1 2
C2 安德魯 1 1 2 2
我應該如何處理這個？
uj5u.com熱心網友回復：
SELECT company_code, ceo, COUNT(DISTINCT lead_code) as total_lead, COUNT(DISTINCT Senior_code) as total_senior, COUNT(DISTINCT Manager_code) as total_manager, COUNT(DISTINCT Employee_Code) as total_employee
FROM
(
SELECT Company.company_code, ceo, Lead.lead_code, Senior.Senior_code, Manager.Manager_code, Employee.Employee_Code
FROM Company
LEFT JOIN Lead
ON Lead.company_code = Company.company_code
LEFT JOIN Senior
ON Senior.company_code = Company.company_code
LEFT JOIN Manager
ON Manager.company_code = Company.company_code
LEFT JOIN Employee
ON Employee.company_code = Company.company_code
)
GROUP BY company_code, ceo;
uj5u.com熱心網友回復：
您可以先聚合，然后與主表連接。例如：
select
c.*, l.c, s.c, m.c, e.c
from company c
left join (select company_code, count(*) as c from lead group by company_code) l on l.company_code = c.company_code
left join (select company_code, count(*) as c from senior group by company_code) s on s.company_code = c.company_code
left join (select company_code, count(*) as c from manager group by company_code) m on m.company_code = c.company_code
left join (select company_code, count(*) as c from employee group by company_code) e on e.company_code = c.company_code
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/497105.html
上一篇：MySQL-將SELECT查詢（WHEREIN）拆分為兩行
下一篇：在MYSQL中計算有條件的用戶