我有如下所述的表格:
subscription_plans(用于存储所有计划的表)
id plan days_limit added_on status rate
------------------------------------------------
1 PlanA 15 1398249706 1 150.00
2 PlanB 15 1398249706 1 150.00
subscriptiond_videos(用于存储每个计划中的视频详细信息的表)
id plan_id videoid
----------------------
1 1 1
2 2 2
subscription_groups(用于存储组的表,其中一个计划可以是另一个计划的一部分,即,计划A是具有两个其他单独计划(计划B和C)的计划)
id plan_id assosiated_plan_id added_on
----------------------------------------------
1 1 2 1398249706
usersubscription(用于存储用户订阅计划的表)
id user_id plan_id subscribed_on
---------------------------------------
1 1 1 1398771106
现在,我的问题是如何获得每个计划的视频数量。如果计划A同时包含计划B和计划C(subscription_groups表),则计数应返回该特定计划中每个计划的总视频计数。现在,我完成了一个查询,该查询将返回计划详细信息以及计划的视频计数,但是我无法将其与subscription_groups结合使用。如何在单个查询中完成此操作。
$data['planquery']=$this->db->query("select
us.plan_id,us.subscribed_on,sp.plan,sp.days_limit,sp.rate,count(sv.videoid) from
usersubscription as us INNER JOIN
subscription_plans as sp ON us.plan_id=sp.id INNER JOIN subscribed_videos as sv ON sp.id=sv.plan_id where sp.status=1 and us.user_id=1");
预期结果:
plan_id subscribed_on plan days_limit rate count
-------------------------------------------------------
1 1398771106 PlanA 15 150.00 2
谁能帮助我找到解决方案?
提前致谢。
你可以这样
SELECT
us.plan_id,
us.subscribed_on,
sp.plan,
sp.days_limit,
sp.rate,
COUNT(sv.videoid)
FROM
usersubscription AS us
RIGHT JOIN subscription_plans AS sp
ON us.plan_id = sp.id
INNER JOIN subscribed_videos AS sv
ON sp.id = sv.plan_id
INNER JOIN subscription_groups g
ON(g.plan_id =sv .plan_id OR sv.plan_id= g.assosiated_plan_id)
WHERE sp.status = 1
AND (us.user_id = 1 OR us.user_id IS NULL )
由于用户仅关联了计划,但关联的计划还可以链接另一个计划,因此最后一个条件将检查用户标识,但对于,则为null;对于第二个链接的计划,由于进行了正确的加入,用户ID将为null subscription_plans
编辑
SELECT
u.plan_id,
u.subscribed_on,
p.plan,
p.days_limit,
p.rate
,COUNT(DISTINCT v.`videoid`)
FROM `usersubscription` u
JOIN `subscription_groups` g
ON (u.`plan_id` = g.`plan_id`)
RIGHT JOIN `subscription_plans` p
ON(u.`plan_id` = p.`id` OR g.`assosiated_plan_id` = p.`id`)
INNER JOIN `subscribed_videos` v ON(v.`plan_id`=g.`assosiated_plan_id` OR u.`plan_id`= v.`plan_id`)
WHERE u.`id`=1 AND p.`status` = 1
对于视频ID,您可以使用group_concat
SELECT
u.plan_id,
u.subscribed_on,
p.plan,
p.days_limit,
p.rate
,COUNT(DISTINCT v.`videoid`) `video_count` ,
GROUP_CONCAT(DISTINCT v.`videoid`) `video_ids`
FROM `usersubscription` u
JOIN `subscription_groups` g
ON (u.`plan_id` = g.`plan_id`)
RIGHT JOIN `subscription_plans` p
ON(u.`plan_id` = p.`id` OR g.`assosiated_plan_id` = p.`id`)
INNER JOIN `subscribed_videos` v ON(v.`plan_id`=g.`assosiated_plan_id` OR u.`plan_id`= v.`plan_id`)
WHERE u.`id`=1 AND p.`status` = 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句