oracle sql - 加入 with 和 group by？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10326706/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle sql - join with having with and group by?
提问by TPR
If I have to find all uses that viewed the same video more than once, then I'm using the following query.
如果我必须找到多次观看同一视频的所有用户，那么我将使用以下查询。
SELECT userid AS users,video_id
FROM watching_list GROUP BY userid , video_id HAVING COUNT(*) > 1;
^^It does give you that, right? If watching_list contains userid and videoid that the user watched and datetime stamp.
^^它确实给了你，对吧？如果watching_list 包含用户观看的userid 和videoid 以及日期时间戳。
What I would like to do is join it to user table, based on userid, to look up user's name etc too and not just the userid.
我想做的是根据用户 ID 将其加入用户表，以查找用户名等，而不仅仅是用户 ID。
I tried doing simple join, and of course it broke over me.
我尝试做简单的连接，当然它让我崩溃了。
回答by Justin Pihony
SELECT Users.Name, Users.userid AS users,video_id
FROM watching_list
JOIN Users
ON Users.UserID = watching_list.UserID
GROUP BY Users.userid , video_id, Users.Name
HAVING COUNT(*) > 1;
Why would this break? It should be the same user, so adding additional info of the same person to the group byshould not cause a change
为什么会这样断？它应该是同一个用户，所以添加同一个人的附加信息group by应该不会导致更改
回答by Marc
A somewhat inelegant way to do it is like so:
一个有点不雅的方法是这样的：
Select USERS.LAST_NAME,
USERS.FIRST_NAME,
double_watchers.VIDEO_ID
FROM USERS,
(SELECT userid AS users,
video_id
FROM watching_list
GROUP BY userid , video_id
HAVING COUNT(*) > 1) double_watchers
Where double_watchers.userid = users.id;
I'm creating an inline table (not sure about terminology) with your original query and then joining it to the USERS table. There's probably a much more efficient way to do it, but I don't have a test instance in front of me to experiment right now.
我正在使用您的原始查询创建一个内联表（不确定术语），然后将其加入到 USERS 表中。可能有一种更有效的方法来做到这一点，但我现在没有一个测试实例来进行实验。
