php - 在单列内计数以获得不同的结果

Question

我正在尝试编写一个稍微复杂的 MySQL 查询，该查询将计算一列中列出的与其他条件相关的不同项目。

目前，该列中的项目只有:MC、SR、MP 和 EV。

这段代码确实有效，但我的问题是，是否有更好、更有效的方法来做到这一点？另外，如果将新项目添加到该列中呢？

$sql = "Select ps.id, ps.primary_skill, 
count(*) 
(select COUNT(*)
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.itemType = 'MC') as mc_count,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.itemType = 'SR') as sr_count,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.itemType = 'MP') as mp_count,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.itemType = 'EV') as ev_count,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.dok = '1') as dok1,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.dok = '2') as dok2,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.dok = '3') as dok3,
(select COUNT(*) 
from items i, item_primary_skill_map ipsm 
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id 
and i.dok = '4') as dok4
FROM primary_skills ps
where grade =  '$pl' and subject='$subject'
";

Answer 1

你可以试试这个:-

Select ps.id, ps.primary_skill, count(*), 
       CASE i.itemType = 'MC' THEN COUNT(*) END AS as mc_count,
       CASE i.itemType = 'SR' THEN COUNT(*) END AS as sr_count,
       CASE i.itemType = 'MP' THEN COUNT(*) END AS as mp_count,
       CASE i.itemType = 'EV' THEN COUNT(*) END AS as ev_count,
       CASE i.dok = '1' THEN COUNT(*) END,
       CASE i.dok = '2' THEN COUNT(*) END,
       CASE i.dok = '3' THEN COUNT(*) END,
       CASE i.dok = '4' THEN COUNT(*) END
from items i, item_primary_skill_map ipsm
where i.id = ipsm.item_id 
and ipsm.skill_id = ps.id FROM 
and grade =  '$pl' 
and subject='$subject'

我认为这就是您问题的解决方案。