我有个问题
return $this->db->select ("SELECT `c_id`,`c_name`,(select count(`a_id`) nam from Admin_course_groups where fk_c_id = `c_id` and fk_g_id = $id)adm from admin_courses");
和输出:
Array
(
[0] => Array
(
[c_id] => 1
[c_name] => Java - Basic Operators
[adm] => 1
)
[1] => Array
(
[c_id] => 3
[c_name] => JAVA Questions and Answers
[adm] => 0
)
[2] => Array
(
[c_id] => 4
[c_name] => Java - Collections Framework
[adm] => 0
)
)
所以我想从这个查询中得到开始日期和结束日期
return $this->db->select ("SELECT `c_id`,`c_name`,count(`a_id`) nam,start_date,end_date from admin_courses join Admin_course_groups on fk_c_id = `c_id` where fk_g_id = $id group by `a_id`");
只返回一个结果
Array
(
[0] => Array
(
[c_id] => 1
[c_name] => Java - Basic Operators
[nam] => 1
[start_date] => 2018-01-01
[end_date] => 2018-09-25
)
)
预期产量
Array
(
[0] => Array
(
[c_id] => 1
[c_name] => Java - Basic Operators
[nam] => 1
[start_date] => 2018-01-01
[end_date] => 2018-09-25
)
[1] => Array
(
[c_id] => 3
[c_name] => JAVA Questions and Answers
[adm] => 0
[start_date] =>
[end_date] =>
)
[2] => Array
(
[c_id] => 4
[c_name] => Java - Collections Framework
[adm] => 0
[start_date] =>
[end_date] =>
)
)
表格管理课程
c_id | C_name | C_desc | C_status
1 Java - Basic Operators test 1
2 JAVA Questions and Answers test2 1
3 Java - Collections Framework test3 1
表管理\u课程\u组
a_id | fk_c_id |fk_g_id|start_date|end_date
1 1 2 2018-01-01 2018-09-25
任何帮助都将不胜感激。
1条答案
按热度按时间xt0899hw1#
您的查询有几个问题,导致它无法返回您想要的所有结果。首先,你使用的是
JOIN
而且只有一个匹配的行admin_course_groups
所以你只能坐一排。你应该使用LEFT JOIN
相反。第二,你是按a_id
你应该什么时候分组c_id
. 最后换了一个LEFT JOIN
您需要修改WHERE
允许的条款fk_g_id
成为NULL
. 此查询应提供所需的结果:输出:
sqlfiddle演示