Wednesday, 15 August 2012

sql - Count rows in table where field is duplicated more than 5 times -


A table lists the user accounts, along with the IP.

I need to run a query that shows more than 5 rows of only one IP.

I had a query, but I lost it

(I have returned the ID, USERNAME and I LAST_IP) - LAST_IP is also where to count the IP Here is an example: enter the @table (user_name, last_ip) values ​​('' value '');

In the @table (user_name, last_ip) value ('xyz') in the user name, which is a thick table (user_id int identity (1, 1), user_name varchar (100), last_p varchar (50)), '192.168.0.XX' , '192.168.0.XX') into the'table (user_name, last_ip) values ​​('dummy', '192.168. 0.XX ') Enter the'table (user_name, last_ip) values ​​(' Harry Potter ',' 192.168.0.XX ') into the @table (user_name, last_ip) values ​​(' that name should not be ') 192.168 Enter '(' user2 ',' ',' 192.168.0.XX ')' table ('user_name, last_ip)' values ​​in the'table (user_name, last_ip) values' User_name, last_ip) values ​​('user1', '192.168.0.YY'), insert the @table (user_name, last_ip) value ('user4', '192.168.0. ) In the @table (user_name, last_ip) value ('user3', '192.168.0.YY') (192.168.0.YY '). ('User_', '192.168.0.YY') Enter the @table (user_name, last_ip) values ​​('user6', '192.168.0.YY'). Enter @table (user_name, last_ip) value ('Peter Pan', '192.168.0.ZZ') in @table (user_name, last_ip) values ​​('tom dick harry', '192.168.0.ZZ') @table ( Enter user_name, last_ip value ('hamti dumpty', '192.168.0.ZZ') @table (user_name, last_ip) values ​​('red rider hood', '192.168.0.ZZ') @ table (user_name, last_ip) ) Enter the value ('Tintin', '192.168.0.ZZ') in @table (user_name, last_ip) values ​​(Enter 'Mickey Mouse', '192.168.0.ZZ') @table (user_name), Last_ip) Value ('Users Only', '192.168.0.AA'). * Select the Last_ip from the Settable Inner Innerction (*)> count as the gate (*) as the ct @ last_ip from the @table group (*) & gt; 5) All. On a .last_ip = allCounts.last_ip

No comments:

Post a Comment