mysql - 根据一列的相似性检查所有其他列中的更改(续)

Question

select sum(case when NumFirstNames <> 1 then 1 else 0 end) as DifferentFirstNames,
   sum(case when NumLastNames <> 1 then 1 else 0 end) as DifferentLastNames,
   sum(case when NumSSN <> 1 then 1 else 0 end) as DifferentSSN,
   sum(case when NumPhone <> 1 then 1 else 0 end) as DifferentPhone       
from (select EncounterId, count(*) as Num,
count(distinct FirstName) as NumFirstNames,
count(distinct LastName) as NumLastNames,
count(distinct SSN) as NumSSN,
count(distinct Phone) as NumPhone
from table t
group by EncounterId) e;

我需要上面的查询按名为 FacilityCode 的表中的另一列进行分组，并按列显示重复 EncounterID 和无缺陷的次数。

此外，除了计数(即第一个查询结果背后的数据)之外，是否可以使用类似构建的查询来提取“缺陷”结果？

请参阅上一个问题的链接: Check for changes in all other columns based on similarities one column

Answer 1

是的。只需将列放在子查询中并在外部查询中聚合即可:

select FacilityCode,
       sum(case when NumFirstNames <> 1 then 1 else 0 end) as DifferentFirstNames,
       sum(case when NumLastNames <> 1 then 1 else 0 end) as DifferentLastNames,
       sum(case when NumSSN <> 1 then 1 else 0 end) as DifferentSSN,
       sum(case when NumPhone <> 1 then 1 else 0 end) as DifferentPhone       
from (select EncounterId, FacilityCode, count(*) as Num,
             count(distinct FirstName) as NumFirstNames,
             count(distinct LastName) as NumLastNames,
             count(distinct SSN) as NumSSN,
             count(distinct Phone) as NumPhone
      from table t
      group by EncounterId, FacilityCode
     ) e
group by FacilityCode;

我不知道你所说的“拉出缺陷结果”是什么意思。但子查询(可能带有合适的 having 子句)将获取每个 EncounterId 的信息。