mysql - 获取具有组条件的查询中的全部行数

Question

我需要在具有 GROUP 条件的查询中包含整数行。这里是查询

SELECT COUNT(*) AS `numrows`
FROM (`exp_channel_titles`)
LEFT JOIN `exp_category_posts` cp ON `cp`.`entry_id`=`exp_channel_titles`.`entry_id`
LEFT JOIN `exp_categories` c ON `cp`.`cat_id`=`c`.`cat_id`
LEFT JOIN `exp_internships_placements` ip ON `ip`.`entry_id`=`exp_channel_titles`.`entry_id`
LEFT JOIN `exp_members` m ON `m`.`member_id`=`exp_channel_titles`.`author_id`
WHERE `exp_channel_titles`.`site_id` =  '8'
AND `exp_channel_titles`.`channel_id` =  '7'
AND (title like '%%')
GROUP BY `exp_channel_titles`.`entry_id`

结果为

numrows
3
2
1
1
1
1
1
1
1
1
1
1

我需要得到 12 个。如果我删除 GROUP 条件，我会得到 15 个不正确的结果。你能告诉我如何让它按照我的需要工作吗？谢谢。

Answer 1

删除 GROUP BY 并改为选择 COUNT(DISTINCT exp_channel_titles.entry_id):

SELECT COUNT(DISTINCT `exp_channel_titles`.`entry_id`) AS `numrows`
FROM (`exp_channel_titles`)
LEFT JOIN `exp_category_posts` cp ON `cp`.`entry_id`=`exp_channel_titles`.`entry_id`
LEFT JOIN `exp_categories` c ON `cp`.`cat_id`=`c`.`cat_id`
LEFT JOIN `exp_internships_placements` ip ON `ip`.`entry_id`=`exp_channel_titles`.`entry_id`
LEFT JOIN `exp_members` m ON `m`.`member_id`=`exp_channel_titles`.`author_id`
WHERE `exp_channel_titles`.`site_id` =  '8'
AND `exp_channel_titles`.`channel_id` =  '7'
AND (title like '%%')