我有一个来自我们网站的登录日志列表,但我需要看看哪个用户ID登录了最多的IP。我们的表如下:
userid,ip,date(unix)
我需要它来输出哪些用户ID的IP登录最多。
我试过这样的东西:
SELECT
userID
FROM loginLogs
GROUP BY userID
HAVING COUNT( DISTINCT ip ) > 1
但这只是显示了一个用户ID的列表。
Select userID, count(distinct ip)
from loginLogs
Group by 1
Order by 2 desc
也许是这样?
SELECT `userID`, count(`ip`) cnt FROM `loginLogs` GROUP BY `userID` HAVING cnt > 1
您可以按不同的值降序排列;
SELECT userID, COUNT(DISTINCT ip) `distinct IP#s`
FROM loginLogs
GROUP BY userID
ORDER BY `distinct IP#s` DESC;
要测试的SQLfiddle。
SELECT userID, COUNT(*) AS count FROM loginLogs
GROUP BY userId ORDER BY count DESC
这将使您的所有用户从最多登录到最少登录。如果要限制结果,请使用LIMIT 1。
您必须对这些结果进行order by COUNT( DISTINCT ip ) desc
排序,并获取第一个Limit 0, 1
SELECT `userID`
FROM `loginLogs`
GROUP BY `userID`
ORDER BY COUNT( DISTINCT `ip` ) desc
LIMIT 0, 1
您可以将子查询中的内容包装起来,以获得用户ID和不同IP的列表。
SELECT DISTINCT ll.`userID`, ll.`ip`
FROM ( SELECT `userID`, COUNT( 1 ) AS Cnt
FROM `loginLogs`
GROUP BY `userID`
HAVING COUNT( DISTINCT `ip` ) > 1 ) id
LEFT JOIN `loginLogs` ll
ON id.`userID` = ll.`userID`
ORDER BY id.`Cnt`;
如果您只想查看拥有最多ips的用户,并且还想查看ips的列表,则可以使用GROUP_CONCAT()
:
SELECT `userID`, group_concat(DISTINCT `ip`)
FROM `loginLogs`
GROUP BY `userID`
ORDER BY COUNT( DISTINCT `ip` ) DESC
LIMIT 1