代码之家 › 专栏 › 技术社区 › Shanu k k

mysql分组问题

mysql

Shanu k k · 技术社区 · 6 年前

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

管理课程组

a_id | fk_c_id |fk_g_id|start_date|end_date
 1      1          2      2018-01-01 2018-09-25

任何帮助都将不胜感激。

1 回复 | 直到 6 年前

Nick 6 年前

您的查询有几个问题,导致它无法返回您想要的所有结果。首先,你使用的是 JOIN 而且只有一个匹配的行 admin_course_groups LEFT JOIN 相反。第二,你是按 a_id 你应该什么时候分组 c_id 左连接 您需要修改 WHERE 允许的条款 fk_g_id 成为 NULL

SELECT `c_id`, `c_name`, COUNT(`a_id`) AS nam, MIN(start_date), MIN(end_date)
FROM admin_courses c
LEFT JOIN admin_course_groups g
ON g.fk_c_id = c.`c_id` 
WHERE fk_g_id = 2 OR fk_g_id IS NULL
GROUP BY `c_id`

输出:

c_id    c_name                          nam     start_date  end_date
1       Java - Basic Operators          1       2018-01-01  2018-09-25
2       JAVA Questions and Answers      0       (null)      (null)
3       Java - Collections Framework    0       (null)      (null)

SQLFiddle Demo