来自别名未知列的 MySQL 总和

Question

我想按 image_gallery 和 video_gallery 两列的 DESC 进行排序。

 SELECT
     b.*,
     c.title as category,
     (SELECT count(*)
      FROM `movie_gallery`
      WHERE parent = b.id) as image_gallery,
     (SELECT count(*)
      FROM `movie_videos`
      WHERE parent = b.id) as video_gallery,
     (image_gallery + video_gallery) as sum_gallery'
FROM
    `movies` b 
LEFT JOIN
    `category` c on c.id = b.category_id
ORDER BY
     sum_gallery DESC

当我尝试将 image_gallery 和 video_gallery 添加在一起以获得 sum_gallery 时，我得到未知列。

如何解决？

Answer 1

您不能在同一个SELECT 语句中引用别名。不要使用相关子查询，而是使用 LEFT JOIN 和分组子查询。

SELECT b.*, c.title AS category, image_gallery, video_gallery, IFNULL(image_gallery, 0) + IFNULL(video_gallery, 0) AS sum_galleries
FROM movies AS b
LEFT JOIN category AS c ON c.id = b.category_id
LEFT JOIN (
    SELECT parent, COUNT(*) AS image_gallery
    FROM movie_gallery
    GROUP BY parent) AS d ON d.parent = b.id
LEFT JOIN (
    SELECT parent, COUNT(*) AS video_gallery
    FROM movie_videos
    GROUP BY parent) AS e ON e.parent = b.id
ORDER BY sum_gallery DESC