SELECT T.LINGUAL_PROFICIENCY,T.LOCATION,TASE.ASE,TSE.SE,TSSE.SSE,TTL.TL,TAM.AM,TM.M,TTOTAL.TOTAL
FROM (SELECT T_EMPLOYEE.LOCATION,T_EMPLOYEE.LINGUAL_PROFICIENCY FROM T_EMPLOYEE GROUP BY LINGUAL_PROFICIENCY,LOCATION) AS T
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS ASE FROM T_EMPLOYEE WHERE EMP_LEVEL = 'ASE' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TASE
ON T.LOCATION = TASE.LOCATION AND T.LINGUAL_PROFICIENCY = TASE.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS SE FROM T_EMPLOYEE WHERE EMP_LEVEL = 'SE' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TSE
ON T.LOCATION = TSE.LOCATION AND T.LINGUAL_PROFICIENCY = TSE.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS SSE FROM T_EMPLOYEE WHERE EMP_LEVEL = 'SSE' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TSSE
ON T.LOCATION = TSSE.LOCATION AND T.LINGUAL_PROFICIENCY = TSSE.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS TL FROM T_EMPLOYEE WHERE EMP_LEVEL = 'TL' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TTL
ON T.LOCATION = TTL.LOCATION AND T.LINGUAL_PROFICIENCY = TTL.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS AM FROM T_EMPLOYEE WHERE EMP_LEVEL = 'AM' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TAM
ON T.LOCATION = TAM.LOCATION AND T.LINGUAL_PROFICIENCY = TAM.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS M FROM T_EMPLOYEE WHERE EMP_LEVEL = 'M' GROUP BY LINGUAL_PROFICIENCY,LOCATION ORDER BY LINGUAL_PROFICIENCY,LOCATION) AS TM
ON T.LOCATION = TM.LOCATION AND T.LINGUAL_PROFICIENCY = TM.LINGUAL_PROFICIENCY
LEFT OUTER JOIN
(SELECT LOCATION,LINGUAL_PROFICIENCY,COUNT(*) AS TOTAL FROM T_EMPLOYEE GROUP BY LINGUAL_PROFICIENCY,LOCATION) AS TTOTAL
ON T.LOCATION = TTOTAL.LOCATION AND T.LINGUAL_PROFICIENCY = TTOTAL.LINGUAL_PROFICIENCY
ORDER BY LOCATION,LINGUAL_PROFICIENCY
用LOCATION的值变化来算分类LOCATION的总数
没有详细数据调试,你自己调试看看
思路很简单,先把最主要的模式确定,就是LOCATION和LANGUAGE,然后算数量,一个个往主模式上拼接
这样一个FOR加上一个内部IF判断就可以搞定了