开发者

SQL Group Select Query

I have the following table:

 ----------- ---------- -----------
| AccountID | Password | IpAddress |
 ----------- ---------- -----------| 
|   1          1234      127.0.0.1 |
|   2          123       127.0.0.1 |
|   3          1234      127.0.0.1 |
|   4          12        127.0.0.2 |
|   5          123       127.0.0.2 |
|   6          12        127.0.0.2 |
|   7          1         127.0.0.2 |
|   8          123       127.0.0.3 |
|   9          123       127.0.0.3 |
 ----------- ---------- -----------

I want to select accountIDs, passwords and IpAddresses from 开发者_如何学编程it grouped by ipaddress where both passwords ipaddresses are the same and have more than one accountid. Rows where more than 1 accountids have the same password and ip. The result of this table would be rows 1,3(ip group 1); 4,6(ip group 2);8,9(group 3).

Thanks.


If I understood you correctly, this is what you want

select t1.* from(select password, IpAddress 
from YourTable
group by password, IpAddress
having count(*) > 1) t2
join YourTable t1 on t1.IpAddress = t2.IpAddress
and t1.password= t2.password
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜