我试图为每个学习者获取一行，格式如下：
nsn|last_name|first_name|middle_name|gender|ethnicities|providers|qualifications|vocation_PI|vocation_SI|.....
学习者有许多提供者，提供者有许多学习者
在每个提供者中，一个学习者有许多结果，结果属于一个学习者
一个结果有一个标准，一个标准属于无/多个结果
标准有许多职业
select l.nsn, l.last_name,l.first_name,l.middle_name, l.gender, GROUP_CONCAT(distinct e.name SEPARATOR '|') as ethnicities, GROUP_CONCAT(distinct p.name SEPARATOR '|') as providers, count(case v.code when 'PI' then 1 else 0 end) as v_PI,count(case v.code when 'SI' then 1 else 0 end) as v_SI,count(case v.code when 'CR' then 1 else 0 end) as v_CR, GROUP_CONCAT(distinct q.name SEPARATOR '|') as qualifications
from learners l
join learner_ethnicity le on l.id = le.learner_id
join ethnicities e on le.ethnicity_id = e.id
join learner_provider lp on l.id = lp.learner_id
join providers p on lp.provider_id = p.id
join results r on lp.id = r.learner_provider_id
join standards s on r.standard_id = s.id
join standard_vocation sv on s.id = sv.standard_id
join vocations v on sv.vocation_id = v.id
join learner_qualification lq on l.id = lq.learner_id
join qualifications q on lq.qualification_id = q.id
group by l.id
我无法计算每个职业的工作次数。
我在每个计数字段中得到相同的值（不确定这是否是所有计数的总和或是什么）
预期产量：
123451234 | Doe | John | James | M | European|Maori | SecondaryProvider|TertiaryProvider | NCEA Level 1|NCEA Level 2|Farming PHD | 185 | 430 | .....
实际产量：
123451234 | Doe | John | James | M | European|Maori | SecondaryProvider|TertiaryProvider | NCEA Level 1|NCEA Level 2|Farming PHD | 500 | 500 | .....
有什么建议吗？
谢谢。
最佳答案
把你的计数换成你想要的总数
select l.nsn, l.last_name,l.first_name,l.middle_name, l.gender, GROUP_CONCAT(distinct e.name SEPARATOR '|') as ethnicities, GROUP_CONCAT(distinct p.name SEPARATOR '|') as providers, sum(case v.code when 'PI' then 1 else 0 end) as v_PI, sum(case v.code when 'SI' then 1 else 0 end) as v_SI,sum(case v.code when 'CR' then 1 else 0 end) as v_CR, GROUP_CONCAT(distinct q.name SEPARATOR '|') as qualifications
from learners l
join learner_ethnicity le on l.id = le.learner_id
join ethnicities e on le.ethnicity_id = e.id
join learner_provider lp on l.id = lp.learner_id
join providers p on lp.provider_id = p.id
join results r on lp.id = r.learner_provider_id
join standards s on r.standard_id = s.id
join standard_vocation sv on s.id = sv.standard_id
join vocations v on sv.vocation_id = v.id
join learner_qualification lq on l.id = lq.learner_id
join qualifications q on lq.qualification_id = q.id
group by l.id
关于mysql - 对联接表进行分组和计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38089360/