mysql - 获取复杂查询的计数结果

Question

假设我有这个 SQL 查询

SELECT max(times_viewed) as m, member_id, (select count(viewed_url) from exp_member_page_tracker  where member_id=mpv.member_id)
FROM `exp_member_page_tracker` mpv
group by member_id

我需要计算所有结果。我就是这样做的

SELECT count(member_id) from ( SELECT max(times_viewed) as m, member_id, (select count(viewed_url) from exp_member_page_tracker where member_id=mpv.member_id)
FROM `exp_member_page_tracker` mpv
group by member_id) as m2

但我不确定这是如何有效的。您能否建议我如何以最佳方式计算结果？

谢谢

Answer 1

对于你的子查询，我是否遗漏了一些东西，这不是效率更高的相同结果吗？在 sqlfiddle 尝试过:

http://sqlfiddle.com/#!2/a03cf/9

你的:

SELECT member_id, max(times_viewed) as max_times_viewed 
, (select count(viewed_url) from exp_member_page_tracker  where member_id=mpv.member_id) as countOfViewedUrl
FROM `exp_member_page_tracker` mpv
group by member_id;

替代方案:

SELECT member_id, max(times_viewed) as max_times_viewed
, count(viewed_url) as countOfViewedUrl
FROM `exp_member_page_tracker` mpv
group by member_id;