mysql - 在mysql中使用同一个表分割多个计数

Question

我有一个以下格式的查询:

SELECT ETM.etm_Taxonomy, COUNT( PE.pp_profileID ) AS total_counts
FROM expertise_taxonomymaster AS ETM
LEFT JOIN expertise_taxonomy AS ET ON ETM.etm_ID = ET.`et_Taxonomy`
LEFT JOIN expertise AS E ON E.et_Taxonomy = ET.`et_ID`
LEFT JOIN profile_expertise AS PE ON PE.pp_expertiseID = E.et_ID
WHERE PE.pp_profileID IN (    
        SELECT PJ.pj_profileID
        FROM jobtitle_taxonomymaster AS JTM
        LEFT JOIN jobtitle_taxonomy AS JT ON JTM.jtm_ID = JT.`jt_Taxonomy`
        LEFT JOIN jobtitle AS J ON J.jt_taxonomy = JT.`jt_ID`
        LEFT JOIN profile_jobtitle AS PJ ON PJ.pj_jobtitleID = J.jt_ID
        WHERE JTM.jtm_Taxonomy = 'Associate'
            OR JTM.jtm_Taxonomy = 'Partner' 
        )
    AND et_lawfirmID in (195,196)
GROUP BY etm_Taxonomy

我的结果如下:

etm_Taxonomy    total_counts
Advertising       18
Antitrust         47    
Banking          258    

但我需要以下结果，计数应根据 JTM.ttm_Taxonomy 字段进行拆分

etm_Taxonomy    Patners195  Partners196     Associates195   Associates196
Advertising       18          18              18              18
Antitrust         47          47              47              47
Banking          258          258            258              258  

Answer 1

试试这个方法:

 SELECT ETM.etm_Taxonomy, 
   SUM (CASE WHEN PJ_TAX.jtm_Taxonomy = 'Associate' THEN 1 ELSE 0 END) AS Associates,
   SUM (CASE WHEN PJ_TAX.jtm_Taxonomy = 'Partner' THEN 1 ELSE 0 END) AS Partners,
   SUM (CASE WHEN PJ_TAX.jtm_Taxonomy = 'Consultant' THEN 1 ELSE 0 END) AS Consultants,
   SUM (CASE WHEN PJ_TAX.jtm_Taxonomy = 'Counsel' THEN 1 ELSE 0 END) AS Counsels,
 COUNT(PE.pp_profileID ) AS total_counts
FROM expertise_taxonomymaster AS ETM
LEFT JOIN expertise_taxonomy AS ET ON ETM.etm_ID = ET.`et_Taxonomy`
LEFT JOIN expertise AS E ON E.et_Taxonomy = ET.`et_ID`
LEFT JOIN profile_expertise AS PE ON PE.pp_expertiseID = E.et_ID
INNER JOIN
 (
SELECT DISTINCT PJ.pj_profileID,JTM.jtm_Taxonomy
FROM jobtitle_taxonomymaster AS JTM
LEFT JOIN jobtitle_taxonomy AS JT ON JTM.jtm_ID = JT.`jt_Taxonomy`
LEFT JOIN jobtitle AS J ON J.jt_taxonomy = JT.`jt_ID`
LEFT JOIN profile_jobtitle AS PJ ON PJ.pj_jobtitleID = J.jt_ID
WHERE JTM.jtm_Taxonomy = 'Associate'
OR JTM.jtm_Taxonomy = 'Partner'
OR JTM.jtm_Taxonomy = 'Consultant'
OR JTM.jtm_Taxonomy = 'Counsel'
) as PJ_TAX
ON  PE.pp_profileID= PJ_TAX.pj_profileID
WHERE et_lawfirmID =195
GROUP BY etm_Taxonomy