mysql - 连接 4 个具有 2 个一对一关系的表

Question

主表“video_index”包含“author_index”和“category_index”的外键。

“feature_main_rel”表包含“video_index”的外键

我想将所有这些内容合并在一起，以便仅显示具有有效作者和类别且处于专题周期内的有效视频。我尝试了以下查询，但出现错误

查询:

SELECT
    *
FROM
    author_index AS a JOIN (
        SELECT
            *
        FROM
            video_index
        JOIN
            feature_main_rel
        ON
            video_index.id = feature_main_rel.video_id
    ) AS fv
        ON a.id = fv.author_id
    JOIN category_index AS c
        ON fv.category_id = c.id
WHERE
    video_index.remove = '0' AND
    video_index.active = '1' AND
    video_index.publish_start <= '$current_time' AND
    video_index.publish_end >= '$current_time' AND
    author_index.remove = '0' AND
    author_index.active = '1' AND
    category_index.remove = '0' AND
    category_index.active = '1' AND
    feature_main_rel.remove = '0' AND
    feature_main_rel.active = '1' AND
    feature_main_rel.start <= '$current_time' AND
    feature_main_rel.end >= '$current_time'
GROUP BY
    video_index.id
ORDER BY
    RAND()
LIMIT
    1

错误:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

Answer 1

http://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

Any columns in the subquery select list must have unique names.

因此，您应该明确命名子选择中的列，因为似乎有一个 id 列，然后与外部 select * 发生冲突