要求：For each user taking a step, calculate how many other users have taken the same step. We are only interested in the cases where the step is performed by at least 5 other users. Display the user ID, number of other users (at least 5 other users) who are taking the same step and the title of the taken step.
以下我写的：
1: 先找出所有taking step 的other user 大于 5 的
2。再找userID
SELECT distinct u.userID,count(u.userID),s.title
FROM user u
INNER JOIN step_taken st ON (st.user_id = u.userID)
INNER JOIN step s ON (st.step_id = s.stepID)
GROUP BY u.userID, s.title
HAVING s.title in (
SELECT s.title
FROM step s
INNER JOIN step_taken st ON (st.step_id = s.stepID)
INNER JOIN user u ON (st.user_id = u.userID)
GROUP BY s.title
HAVING count(u.userID) - 1 > 5
)
结果跟预期不太一样，希望能帮我找出问题出在哪里，并修改一下，谢谢。
重做了一下修改：
先找other users 的数量和对应的 step_num的表
再INNERJOIN
SELECT distinct st.user_id, other_users_nums, s.title
FROM step_taken st
INNER JOIN step s ON (st.step_id = s.stepID)
INNER JOIN (SELECT count(distinct(user_id)) - 1 AS other_users_nums,step_id
FROM step_taken
GROUP BY step_id
HAVING other_users_nums >= 5) as other_user
ON (s.stepID = other_user.step_id)
这样是不是感觉对了一些
