Is it possible to implement this complex query using SQL SELECT statements?-CodePudding

To prevent bulk registration (the site uses a WordPress program), I installed a "

I don't know much about SQL SELECT statement, is it possible to use SQL SELECT statement to get all registered IPs and sort them by the number of IP appearances? For example, query the 10 most registered IPs, so that I can find suspicious IPs and block them in time.

This is my current attempt and I don't know how to sort by IP occurrences. Any help, thanks in advance!

SELECT * 
FROM wp_usermeta 
WHERE meta_key = 'signup_ip' 
ORDER BY meta_value ASC 
LIMIT 0, 10;

CodePudding user response：

You may aggregate by the user_id and then pivot out the IP address for use in sorting.

SELECT ip, COUNT(*) AS cnt
FROM
(
    SELECT MAX(CASE WHEN meta_key = 'signup_ip' THEN meta_value END) AS ip
    FROM wp_usermeta
    GROUP BY user_id
) t
GROUP BY ip
ORDER BY cnt DESC
LIMIT 10;