SQL Group By可能的子查询？(SQL Group By with possible subquery?)

SQL Group By可能的子查询？(SQL Group By with possible subquery?)

我有一个User表，有1个用户关系的记录。 我想找到UserId相同且UserName不同的所有记录。

例如我尝试过：

SELECT UserId, UserName, count(*) FROM tblUser GROUP BY UserId, UserName

这只是给我一个用户列表以及他们有多少次UserId和UserName的独特组合

所以我想要的输出就是

UserId | UserName ----------+----------- 1111 | Bob 1111 | JoeBob 2222 | Jimmy 2222 | ILikeTurtles

I have a User table that has a many records to 1 user relationship. I want to find all the records where the UserId's are the same and the UserName's are different.

for example I have tried:

SELECT UserId, UserName, count(*) FROM tblUser GROUP BY UserId, UserName

This just gives me a list of users and how many times they have that distinct combination of UserId and UserName

so my desired output would be

UserId | UserName ----------+----------- 1111 | Bob 1111 | JoeBob 2222 | Jimmy 2222 | ILikeTurtles

最满意答案

select * from tblUser t1 inner join ( SELECT UserId FROM tblUser GROUP BY UserId having count(distinct UserName) > 1 ) t2 on t1.UserID = t2.UserID select * from tblUser t1 inner join ( SELECT UserId FROM tblUser GROUP BY UserId having count(distinct UserName) > 1 ) t2 on t1.UserID = t2.UserID