mysql - 当我将内连接更改为左连接时，sql错误1064

Question

我写sql

SELECT 
    count(*) as count,g.name
FROM
    test_scale g
        INNER JOIN
    test_user_scale_result a
        INNER JOIN
    en_org_user_department b
        INNER JOIN
    en_org_department d
        INNER JOIN
    test_scale_type e ON a.user_id = b.user_id
        AND g.id = a.scale_id
        AND b.department_id = d.id
        AND d.position LIKE CONCAT((SELECT 
                    position
                FROM
                    en_org_department
                WHERE
                    id = 8),
            '%')
        AND e.id = g.scale_type_id
        and b.status = 1
        and g.scale_type_id IN (1 , 9)
        and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45'
        group by a.scale_id;

我运行正确。但是当我将内部更改为左侧时，就像

SELECT 
    count(*) as count,g.name
FROM
    test_scale g
        left JOIN
    test_user_scale_result a
        left JOIN
    en_org_user_department b
        left JOIN
    en_org_department d
        left JOIN
    test_scale_type e ON a.user_id = b.user_id
        AND g.id = a.scale_id
        AND b.department_id = d.id
        AND d.position LIKE CONCAT((SELECT 
                    position
                FROM
                    en_org_department
                WHERE
                    id = 8),
            '%')
        AND e.id = g.scale_type_id
        and b.status = 1
        and g.scale_type_id IN (1 , 9)
        and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45'
        group by a.scale_id;

it error 20:37:15 SELECT * FROM test_user_scale_result a
left JOIN en_org_user_department b left JOIN
en_org_department d left JOIN test_scale_type e left JOIN test_scale g ON a.user_id = b.user_id AND g.id = a.scale_id AND b.department_id = d.id AND d.position LIKE CONCAT((SELECT position FROM en_org_department WHERE id = 8),
'%') AND e.id = g.scale_type_id and b.status = 1
and g.scale_type_id IN (1 , 9) and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45' Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 26 0.0060 sec

Answer 1

我认为 left join 需要紧跟在连接后面的 on 子句。真的，无论如何你都应该这样做:

SELECT 
    count(*) as count,g.name
FROM
    test_scale g left JOIN
    test_user_scale_result a
    ON g.id = a.scale_id AND a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45' left JOIN
    en_org_user_department b
    ON a.user_id = b.user_id AND b.status = 1 left JOIN
    en_org_department d
    ON b.department_id = d.id AND
       d.position LIKE CONCAT((SELECT position FROM en_org_department WHERE id = 8),
                              '%') left JOIN
    test_scale_type e
    ON e.id = g.scale_type_id
WHERE g.scale_type_id IN (1 , 9)
group by a.scale_id;

注意:

• 我非常确定特定表的条件需要放在该表首次出现的 ON 子句中。我认为如果将所有内容都填充在最后的 on 子句中，left join 就不会起作用。
• 第一个表的条件应位于 where 子句中。否则，你会得到奇怪的结果。
• 以下是使用连接时的一些简单规则。 (1) 每个 JOIN 后始终有一个 ON 子句，CROSS JOIN 除外。 (2) 切勿在 FROM 子句中使用逗号(您没有这样做，但我总是将其放入)。