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
精彩评论