表格"计划"
id name 1 abc 2 def 3 ghi
表"时间表"
id fk_store fk_plan 1 4 1 2 4 1 3 4 1 4 6 1 5 6 1 6 5 3 7 7 3 8 7 3 9 12 1
这是我的问题:
SELECT plan.id as id, name,
(SELECT GROUP_CONCAT(cnt) cnt FROM (SELECT COUNT(*) cnt
FROM schedule WHERE fk_plan = plan.id GROUP BY fk_store) q) as schedule_count
FROM plan LEFT JOIN schedule ON plan.id = schedule.fk_plan GROUP BY plan.id
我期待这个结果(例如在数组中):
[0] = array(
['id'] = 1,
['name'] = 'abc',
['schedule_count'] = '3, 2, 1'
),
[1] = array(
['id'] = 2,
['name'] = 'def',
['schedule_count'] = ''
),
[2] = array(
['id'] = 3,
['name'] = 'ghi',
['schedule_count'] = '2,1'
),
不幸的是,我收到一个错误,WHERE子句中的列plan.id未定义。当我不使用WHERE时,结果"schedule_count"在每个数组中都是"3,2,2,1,1"。我找到了许多类似的解决方案,但我无法修复它。
我希望我的问题很清楚。提前谢谢。
尝试这个
SELECT plan.id, name , GROUP_CONCAT(cnt) AS schedule_count
FROM plan left join (select fk_plan, count(fk_store) as cnt from schedule
GROUP BY fk_store)t
ON t.fk_plan = plan.id
GROUP BY plan.id
FIDDLE中的演示
如果显示0而不是
(null)
,则用此替换上述GROUP_CONCTGROUP_CONCAT(if(cnt is null , 0 , cnt))
使用联接:-
SELECT plan.id as id, name, GROUP_CONCAT(PlanCnt)
FROM plan
LEFT OUTER JOIN (SELECT fk_plan, COUNT(DISTINCT fk_store) AS PlanCnt FROM schedule GROUP BY fk_plan) Sub1
ON plan.id = Sub1.fk_plan
GROUP BY plan.id as id, plan.name