最新项目中需要查询分组过滤并统计审批单子数量,需要用到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的组合使用》对你有帮助,请点赞、收藏,并留下你的观点哦!