多条件分类统计group by 显示数目为0的类别

时间：2023-03-09 02:50:03
多条件分类统计group by 显示数目为0的类别 
CREATE TABLE #authorTable(author VARCHAR(50))

INSERT #authorTable

SELECT 'peter' UNION

SELECT '捌妮' UNION

SELECT '斑斓' UNION

SELECT '初三' UNION

SELECT '老司机' UNION

SELECT '梦想家' UNION

SELECT '三三酱' UNION

SELECT '甜甜' UNION

SELECT '托尼' UNION

SELECT '汪洋' UNION

SELECT '维也纳' UNION

SELECT '夏雨天' UNION

SELECT '小赤' UNION

SELECT '小叮当' UNION

SELECT '小三' UNION

SELECT '肖肖' UNION

SELECT '张三崩' 

SELECT * FROM #authorTable

SELECT author, COUNT(IA_ID) FROM dbo.InfoArticle ia

LEFT JOIN #authorTable ON ia.IA_Author=#authorTable.author

WHERE  IA_State=1 AND IA_IsImport=0  AND IA_CreateDate BETWEEN '2017-12-1' AND '2017-12-2'

GROUP BY  all author

HAVING author  IS NOT NULL

ORDER by  CHARINDEX(','+CONVERT(varchar(10),author)+',',',peter,小赤,肖肖,甜甜,朱朱,斑斓,汪洋,三三酱,peter,着火,梦想家,托尼,维也纳,夏雨天,捌妮,老司机,小三,张三崩,小叮当,初三,')