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 | ILikeTurtlesI 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, UserNameThis 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更多推荐
发布评论