子查询-查找具有不同ID - Oracle的副本

Question

我有一张桌子，上面有牌照号码的列表。许可号分配给ID，ID可以具有相同号码的多个许可证(有些是过期的或不同的类型等等)。

一些牌照号码的前面有一个'B‘。如果我要删除'B‘，我想知道有多少许可证#被复制到不同的ID。

到目前为止，我已经去除了B和得到的计数，但现在我需要更进一步，只需要查询什么是重复之间的ID。

SQL

SELECT 
COUNT(*), TININDIV_IS_NUMBER as ID,
CASE
    WHEN SUBSTR (LICENSE_NO, 1, 1) <> 'B'
    THEN LICENSE_NO
    ELSE SUBSTR(LICENSE_NO, 2, LENGTH(LICENSE_NO)) 
END AS LICENCSE_NO
FROM BF_CERTIFICATE 
GROUP BY TININDIV_IS_NUMBER, LICENSE_NO 

输出示例

​

Answer 1

，我想知道有多少许可证#被复制到不同的ID。

您可以获得许可证#s的列表如下：

SELECT (CASE WHEN SUBSTR (LICENSE_NO, 1, 1) <> 'B'
             THEN LICENSE_NO
             ELSE SUBSTR(LICENSE_NO, 2, LENGTH(LICENSE_NO)) 
        END) AS LICENCSE_NO,
       COUNT(DISTINCT TININDIV_IS_NUMBER)
FROM BF_CERTIFICATE 
GROUP BY (CASE WHEN SUBSTR (LICENSE_NO, 1, 1) <> 'B'
               THEN LICENSE_NO
               ELSE SUBSTR(LICENSE_NO, 2, LENGTH(LICENSE_NO)) 
          END) 
HAVING COUNT(DISTINCT TININDIV_IS_NUMBER) >= 2;

注意：CASE表达式需要在GROUP BY中重复，因为GROUP BY子句中不识别列别名。

如果您想要实际的计数，那么只需使用上面的子查询并在外部查询中放置一个count(*)。

Answer 2

我想知道有多少许可证#被复制到不同的ID

您可以使用两个级别的聚合来获取重复项的计数：

select count(*)
from (
    select 1
    from bf_certificate 
    group by case
        when substr (license_no, 1, 1) <> 'b'
        then license_no
        else substr(license_no, 2, length(license_no)) 
    end
    having count(*) = 1
) t

如果您想要重复的许可证号码列表，那么一个级别的聚合就足够了：

select 
    case
        when substr (license_no, 1, 1) <> 'b'
        then license_no
        else substr(license_no, 2, length(license_no)) 
    end license_no,
    count(*)
from bf_certificate 
group by case
    when substr (license_no, 1, 1) <> 'b'
    then license_no
    else substr(license_no, 2, length(license_no)) 
end
having count(*) = 1