DISTINCT和COUNT的组合使用

清疚 2022-11-05 04:27 180阅读 0赞

最新项目中需要查询分组过滤并统计审批单子数量，需要用到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'

查询结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]

# 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

查询结果如下  
![在这里插入图片描述][20210304090906491.png]

# 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;

查询结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: /images/20221023/7323b5306ff74bfa8697c1e664006288.png
[20210304090906491.png]: /images/20221023/2ba76797724946b6b74ea1421879f747.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]: /images/20221023/e06b7ba3b0e14b499a718d6d084851d9.png