mysql - 子查询:on 子句不明确

Question

SELECT * 
FROM (
    `lecture` AS l
)
LEFT JOIN  `professor` AS p ON  `p`.`professor_id` =  `l`.`professor_id` 
WHERE  `lecture_sem` =  '20141'
AND (
    lecture_name LIKE  '%KEYWORD%'
    OR lecture_code LIKE  '%KEYWORD%'
    OR p.professor_name LIKE  '%KEYWORD%'
)
AND (
    SELECT COUNT( DISTINCT s1.yoil, s1.start_time, s1.end_time ) 
    FROM schedule AS s1
    INNER JOIN schedule AS s2 ON ( s1.lecture_id
    IN (
        SELECT lecture_id
        FROM timeitem
        WHERE timetable_id =890
    )
AND s2.yoil = s1.yoil
AND (
    (
    s1.start_time <= s2.start_time
    AND s2.end_time <= s1.end_time
    ) ) 
    AND s2.lecture_id = lecture_id # <-- HERE
    )
) >0
LIMIT 0 , 30

我想像这样使用 where 列:

s2.lecture_id = lecture_id

或者，

s2.lecture_id = l.lecture_id

所以我想在子查询中使用父列，但是出现错误:

Column 'lecture_id' in on clause is ambiguous

我用谷歌搜索了很多关于这个问题的答案(“on 子句不明确”)，他们说我应该将这个查询替换为加入两个查询。但我不知道如何转换此查询。

Answer 1

我相信下面的查询是等效的，但我还没有测试过。

技术是将相关子查询作为派生表移动到 FROM 子句中，这样它只运行一次，为每个 lecture_id 产生一个结果(因此是 GROUP BY)。

我还提取了时间表的子查询，我相信它可以重写为 JOIN。

而且我怀疑对教授的连接可能是一个 INNER JOIN——没有教授你怎么能上课？

SELECT l.*, p.*
FROM lecture AS l
INNER JOIN professor AS p ON  p.professor_id =  l.professor_id 
INNER JOIN (
    SELECT s2.lecture_id, COUNT( DISTINCT s1.yoil, s1.start_time, s1.end_time ) AS count
    FROM schedule AS s1
    INNER JOIN schedule AS s2 ON s2.yoil = s1.yoil 
        AND s1.start_time <= s2.start_time AND s2.end_time <= s1.end_time
    INNER JOIN timeitem AS t ON s1.lecture_id = t.lecture_id
    WHERE t.timetable_id = 890
    GROUP BY s2.lecture_id
) AS c ON l.lecture_id = c.lecture_id
WHERE l.lecture_sem =  '20141' 
    AND c.count > 0 
    AND (
        l.lecture_name LIKE  '%KEYWORD%'
        OR l.lecture_code LIKE  '%KEYWORD%'
        OR p.professor_name LIKE  '%KEYWORD%'
    )
LIMIT 0 , 30

无论如何，即使查询不完美，它也演示了如何重构它以避免相关子查询。