使用 SQL 中的 where 子句计算字段数量

Question

是否可以对某些字段进行计数？

我有以下查询：（

SELECT * FROM 
(SELECT messages.id as id,
       messages.user_id,
       messages.category_id,
       messages.parent_id,
       messages.message,
       messages.create_date,
       messages.update_date,
       messages.status,
       users.name as username,
       users.id as userid,
       users.email as useremail,
       users.phone as userphone,
       users.active as useractive,
       users.role as userroel,
       users.date as register_date
FROM `afo_messages` as messages 
INNER JOIN `key_users` as users ON messages.user_id = users.id
WHERE messages.category_id=5 AND messages.parent_id=0
ORDER BY messages.id DESC LIMIT 30) ilv 
ORDER BY id ASC

这是输出示例： http://lab.mirgorod.us/res.pdf ）

是否可以在此查询中再添加一列：子消息计数（parent_id = 父消息的 id）？

Answer 1

您可以使用子查询轻松完成：

SELECT * FROM 
(SELECT messages.id as id,
       messages.user_id,
       messages.category_id,
       messages.parent_id,
       messages.message,
       messages.create_date,
       messages.update_date,
       messages.status,
       users.name as username,
       users.id as userid,
       users.email as useremail,
       users.phone as userphone,
       users.active as useractive,
       users.role as userroel,
       users.date as register_date,
       (SELECT COUNT(*) FROM afo_messages WHERE parent_id=messages.id) AS `sub messages`
FROM `afo_messages` as messages 
INNER JOIN `key_users` as users ON messages.user_id = users.id
WHERE messages.category_id=5 AND messages.parent_id=0
ORDER BY messages.id DESC LIMIT 30) ilv 
ORDER BY id ASC

Answer 2

尝试将其添加为一列：

child_count = (select count(*) from afo_messages where parent_id=messages.id)