提问人：Carlos Morales 提问时间：4/23/2014 更新时间：4/28/2014 访问量：139
如何在重复语句中显示 0？
How can show 0 in a repeated statement?
问：
我正在尝试显示所有策略业务部门，但仅在存在值时显示
我的桌子：
|policies|
|id| |client| |policy_business_unit_id| |cia_ensure_id| |state|
1 MATT 1 1 0
2 STEVE 1 1 0
3 BILL 1 2 0
4 LARRY 1 2 1
5 MATT 1 2 1
6 STEVE 2 2 2
7 BILL 2 2 2
8 LARRY 2 2 1
9 MATT 2 1 1
|policy_business_units|
|id| |name| |comercial_area_id|
1 LIFE 1
2 ROB 1
3 CAR 1
4 RARE 1
5 RARE2 1
|comercial_areas|
|id| |name|
1 MICROSOFT
2 APPLE
|cia_ensures|
|id| |name|
1 ORANGE
2 BT
3 ATT
4 MOVISTAR
5 SPRINT
我得到这个结果，没关系，但没有显示所有policy_business单位
UNIT V1 A1 N1 V2 A2 N2 V3 A3 N3 V4 A4 N4 V5 A5 N5
LIFE 2 0 0 1 2 0 0 0 0 0 0 0 0 0 0
ROB 0 1 0 0 1 2 0 0 0 0 0 0 0 0 0
下面是查询 http://sqlfiddle.com/#!2/6909d8/1
set @@local.group_concat_max_len=10000;
select distinct @sql := concat('SELECT pb.name as unit,',
group_concat(concat(
'SUM(CASE WHEN p.state =0 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS v',id,',
SUM(CASE WHEN p.state =1 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS a',id,',
SUM(CASE WHEN p.state =2 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS n',id)),
'
FROM cia_ensures ce
LEFT JOIN policies p on ce.id = p.cia_ensure_id
INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
AND ca.id=1
Group by p.policy_business_unit_id;')
from cia_ensures
where id in(1,2,3,4,5,6,7,8,10);
prepare stmt from @sql;
execute stmt;
我试图展示这一点：
UNIT V1 A1 N1 V2 A2 N2 V3 A3 N3 V4 A4 N4 V5 A5 N5
LIFE 2 0 0 1 2 0 0 0 0 0 0 0 0 0 0
ROB 0 1 0 0 1 2 0 0 0 0 0 0 0 0 0
RARE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RARE2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
请有人能帮我吗？
我将不胜感激所有的帮助。
答：
1赞
Zafar Malik
4/28/2014
#1
如果对您有帮助，请检查以下更改，我没有执行它只是在查询中进行了一些更改，因此需要在您最后检查：
set @@local.group_concat_max_len=10000;
select distinct @sql := concat('SELECT pb.name as unit,',
group_concat(concat(
'SUM(CASE WHEN p.state =0 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS v',id,',
SUM(CASE WHEN p.state =1 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS a',id,',
SUM(CASE WHEN p.state =2 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS n',id)),
'
FROM
policy_business_units pb
LEFT JOIN policies p
ON pb.id = p.policy_business_unit_id
LEFT JOIN cia_ensures ce
on ce.id = p.cia_ensure_id
LEFT JOIN comercial_areas ca
ON ca.id = pb.comercial_area_id
AND ca.id=1
Group by pb.name;')
from cia_ensures
where id in(1,2,3,4,5,6,7,8,10);
prepare stmt from @sql;
execute stmt;
评论
INNER JOIN policy_business_unitsRIGHT JOIN policy_business_unitspb.name