最新项目中需要查询分组过滤并统计审批单子数量，需要用到DISTINCT和COUNT函数，特此记录如下：

1：单独使用DISTINCT过滤分组

SELECT DISTINCT(b.short_name) AS short_name,b.WFCODEFROM SUMMARY_DOCUMENT_AUDIT aLEFT JOIN FORMFUNCTION bON upper(a.WFCODE) = upper(b.WFCODE)WHERE SOURCE ='EIP'AND APPROVER_ID = '60052760'

查询结果如下

2：单独使用COUNT统计审批单子数量

SELECT COUNT(b.WFCODE) num,b.WFCODEFROM SUMMARY_DOCUMENT_AUDIT aLEFT JOIN FORMFUNCTION bON upper(a.WFCODE) = upper(b.WFCODE)WHERE SOURCE ='EIP'AND APPROVER_ID ='60052760'GROUP BY b.WFCODE

查询结果如下

3：既分组又统计出数量可以组合使用

SELECT AA.WFCODE,BB.NUM,AA.short_nameFROM( SELECT DISTINCT(b.short_name) AS short_name,b.WFCODEFROM SUMMARY_DOCUMENT_AUDIT aLEFT JOIN FORMFUNCTION bON upper(a.WFCODE) = upper(b.WFCODE)WHERE SOURCE ='EIP'AND APPROVER_ID = '60052760') AA,(SELECT COUNT(b.WFCODE) num,b.WFCODEFROM SUMMARY_DOCUMENT_AUDIT aLEFT JOIN FORMFUNCTION bON upper(a.WFCODE) = upper(b.WFCODE)WHERE SOURCE ='EIP'AND APPROVER_ID ='60052760'GROUP BY b.WFCODE) BBWHERE AA.WFCODE = BB.WFCODE;

查询结果如下

如果觉得《DISTINCT和COUNT的组合使用》对你有帮助，请点赞、收藏，并留下你的观点哦！