我有一个非常简单的提交表：userId，submittingGuid

我想选择表中具有10个以上提交的所有用户的用户名(使用简单的内部联接即可)。

我会用嵌入式查询和一个分组来计数提交次数...但是有更好的方法(没有嵌入式查询)吗？

谢谢！

解决方案

select userId, count(*)
from   submissions
having count(*) > 10
group by userId

我相信这是最简单的方法：

select userId
from submission   
group by userId
having count(submissionGuid) > 10

SELECT 
    username 
FROM 
    usertable 
    JOIN submissions 
        ON usertable.userid = submissions.userid 
GROUP BY 
    usertable.username 
HAVING 
    Count(*) > 1

*假设"用户"表称为用户表，并且其列名为"用户名"

我认为正确的查询是这样的(SQL Server)：

SELECT s.userId, u.userName
FROM submission s INNER JOIN users u on u.userId = s.userId   
GROUP BY s.userId, u.username
HAVING COUNT(submissionGuid) > 10

如果我们没有HAVING子句：

SELECT u.userId, u.userName
FROM users u INNER JOIN (
    SELECT userId, COUNT(submissionGuid) AS cnt
    FROM submission
    GROUP BY userId ) sc ON sc.userId = u.userId
WHERE sc.cnt > 10

选择用户标识，count(submissionGUID)作为submitCount

从提交

按用户ID分组，submitCount

提交计数> 10