SQL中匹配和不匹配的列值的Count No

Question

我有一个数据集，在其中我试图找到列中匹配的值的计数和不匹配的值计数。我也在努力寻找相同和不同的价值观。例如

ID  F1 F2 F3   
A1  B1  D1  10  
A1  B1  D1  10  
A1  B1  D1  10  
A1  B1  D1  20  
A1  B1  D1  20  
A1  B1  D1  20  
A1  C1  D2  10  
A1  C1  D2  10  
A1  C1  D2  10  
A1  C1  D2  10  
A1  C1  D2  20  
A1  C1  D2  20  
A1  C1  D2  20  
A1  C1  D2  20  
A1  C1  D2  30  
A1  C1  D2  30  

ID始终相同。列F1和F2将更改值。我需要为ID找出在每组F1和F2中有多少列F3值相同和不同。还有这些价值观是什么。预期输出为

ID  F1 F2  Count F3  Count          F3 F3 values    F3 values   
matched   not matched    matched         not matched
----------------------------------------------------------------
A1  B1 D1  2         1              10,20           30  
A1  C1 D2  2         1              10,20           30  

我尝试过的示例代码是：

select ID, F1, F2, group_concat(F3,'|') from  table 
group by ID, F1,F2.

在那之后，我离开了，加入了同一个表，以获得不同的值和计数，但进展并不顺利。非常感谢您的帮助。

谨致问候，

Answer 1

哇，解决了它。看起来有点复杂的查询：

select 
ID, F1, F2,
group_concat(if(all_groups=1,F3, null)) all_matched_F3,
count(if(all_groups=1,F3, null)) all_matched_F3_count,
group_concat(if(all_groups=0,F3, null)) not_all_matched_F3,
count(if(all_groups=0,F3, null)) not_all_matched_F3_count
from (
select 
F3,
count(distinct concat(ID, F1, F2)) = (select count(distinct concat(ID, F1, F2)) from data) all_groups
from data
group by F3
) tmp,
(select distinct ID, F1, F2 from data) groups
group by  ID, F1, F2
;

查询有效。可以在这里测试SQLize.online

所有的查询优化欢迎在评论

Answer 2

符合您需求的较短版本：

select count(distinct data.F3) from data INTO @numOfF3s;
select 
data.ID, 
data.F1, 
data.F2, 
count(distinct data.F3) as 'match F3',
@numOfF3s - count(distinct data.F3) as 'no match F3'
from  data 
group by data.ID, data.F1, data.F2

给出：

+----+----+----+----------+-------------+
| ID | F1 | F2 | match F3 | no match F3 |
+----+----+----+----------+-------------+
| A1 | B1 | D1 |        2 |           1 |
| A1 | C1 | D2 |        3 |           0 |
+----+----+----+----------+-------------+