mysql - 在 select 和 from 子句中使用子查询出现 sql 错误

Question

当我在 select 和 from 子句中使用子查询时出现错误
错误:https://www.awesomescreenshot.com/image/3069216/0505cad495528e9f9af2281ea281415c .

这是我到目前为止所做的:

SELECT m.id, m.title_name, m.release_date, m.plot, m.poster, 
  (SELECT count(*) FROM users_ratings WHERE type_id = m.id AND type_name = 'movies' LIMIT 1) count_user_rating 
FROM (SELECT u.* FROM users u
      LEFT JOIN users_ratings ur ON ur.user_id = u.id
      WHERE ur.type_id = 2
      AND ur.type_name = 'movies') u
LEFT JOIN users_ratings ur ON ur.user_id = u.id
LEFT JOIN movies m ON m.id = ur.type_id
WHERE ur.type_name = 'movies'

UNION

SELECT s.id, s.title_name, s.first_air_date release_date, s.plot, s.poster, 
  (SELECT count(*) FROM users_ratings WHERE type_id = s.id AND type_name = 'series' LIMIT 1) count_user_rating 
FROM (SELECT u.* FROM users u
      LEFT JOIN users_ratings ur ON ur.user_id = u.id
      WHERE ur.type_id = 2
      AND ur.type_name = 'movies') u
LEFT JOIN users_ratings ur ON ur.user_id = u.id
LEFT JOIN series s ON s.id = ur.type_id
WHERE ur.type_name = 'series'

Answer 1

不确定您的查询有什么问题，对我来说似乎很好。但这里有另一种方法来实现它。试试这个

SELECT m.id,
       m.title_name,
       m.release_date,
       m.plot,
       m.poster,
       t.count_user_rating
FROM   (SELECT u.*
        FROM   users u
               LEFT JOIN users_ratings ur
                      ON ur.user_id = u.id
        WHERE  ur.type_id = 2
               AND ur.type_name = 'movies') u
       LEFT JOIN users_ratings ur
              ON ur.user_id = u.id
       LEFT JOIN movies m
              ON m.id = ur.type_id
       LEFT JOIN (SELECT type_id,
                         Count(*) AS count_user_rating
                  FROM   users_ratings
                  WHERE  NAME = 'movies'
                  GROUP  BY type_id) t
              ON t.type_id = m.id
WHERE  ur.type_name = 'movies'
UNION
SELECT s.id,
       s.title_name,
       s.first_air_date release_date,
       s.plot,
       s.poster,
       t.count_user_rating
FROM   (SELECT u.*
        FROM   users u
               LEFT JOIN users_ratings ur
                      ON ur.user_id = u.id
        WHERE  ur.type_id = 2
               AND ur.type_name = 'movies') u
       LEFT JOIN users_ratings ur
              ON ur.user_id = u.id
       LEFT JOIN series s
              ON s.id = ur.type_id
       LEFT JOIN (SELECT type_id,
                         Count(*) AS count_user_rating
                  FROM   users_ratings
                  WHERE  NAME = 'series'
                  GROUP  BY type_id) t
              ON t.type_id = s.id
WHERE  ur.type_name = 'series'