DISTINCT和COUNT的组合使用

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

1：单独使用DISTINCT过滤分组

  SELECT DISTINCT(b.short_name) AS short_name,
         b.WFCODE
  FROM SUMMARY_DOCUMENT_AUDIT a
  LEFT JOIN FORMFUNCTION b
  ON upper(a.WFCODE) = upper(b.WFCODE)
  WHERE SOURCE       ='EIP'
  AND APPROVER_ID    = '60052760'

查询结果如下

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

  SELECT COUNT(b.WFCODE) num,
         b.WFCODE
  FROM SUMMARY_DOCUMENT_AUDIT a
  LEFT JOIN FORMFUNCTION b
  ON 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_name
FROM
  ( SELECT DISTINCT(b.short_name) AS short_name,
    b.WFCODE
  FROM SUMMARY_DOCUMENT_AUDIT a
  LEFT JOIN FORMFUNCTION b
  ON upper(a.WFCODE) = upper(b.WFCODE)
  WHERE SOURCE       ='EIP'
  AND APPROVER_ID    = '60052760'
  ) AA,
  (SELECT COUNT(b.WFCODE) num,
    b.WFCODE
  FROM SUMMARY_DOCUMENT_AUDIT a
  LEFT JOIN FORMFUNCTION b
  ON upper(a.WFCODE) = upper(b.WFCODE)
  WHERE SOURCE       ='EIP'
  AND APPROVER_ID    ='60052760'
  GROUP BY b.WFCODE
  ) BB
WHERE AA.WFCODE = BB.WFCODE;

查询结果如下