mysql - 选择语句中的子查询返回 SQL 错误

Question

我收到此查询的 SQL 错误

Error Number: 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 '*)
FROM `cast_gallery` q
JOIN `all_media` r on q.all_media_id=r.id
WHERE q.pa' at line 1

SELECT `cast_name`, `cast_slug`, (SELECT count(q.*)
 FROM `cast_gallery` q
 JOIN `all_media` r on q.all_media_id=r.id
 WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, `view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

如何解决？

Answer 1

要计算总行数，我们使用 COUNT(*) ，而不是 Count(q.*)。此外，From 子句中的 casts 也不需要括号。

SELECT `cast_name`, 
       `cast_slug`, 
       (SELECT count(*)
        FROM `cast_gallery` q
        JOIN `all_media` r on q.all_media_id=r.id
        WHERE q.parent = `casts`.`id` AND 
              r.approved=1) as image_gallery, 
      `active`, 
      `view`, 
      `checked`, 
      `sex`, 
      `id`
FROM `casts`
ORDER BY `cast_name` asc
LIMIT 10

附加信息:

COUNT(...) 用于统计非NULL值的个数；然而 COUNT(*) 将计算检索到的行总数，无论它们是否包含 NULL 值。