bearhunterUA bearhunterUA - 3 months ago 6
MySQL Question

MYSQL select only users who made more than 1 record, without grouping

I have UserLogs table and query for logs monitoring

SELECT from_unixtime(logDate/1000) as logDate, userId, userAction FROM UserLogs
WHERE logDate > subdate(now(), INTERVAL 1 day )


I want to improve it with showing in result only users who have more than 1 record, without grouping.

Example:



User A made 1 record for a day, user B made 5 records and user C made 3 records for same day. SQL result will show 8 columns - 5 records from user B and 3 records from user C.






<table border='1'>
<tr>
<td>UserId</td>
<td>Time</td>
<td>Action</td>
</tr>
<tr>
<td>B</td>
<td>11.56</td>
<td>POST</td>
</tr>
<tr>
<td>B</td>
<td>11.57</td>
<td>DELETE</td>
</tr>
<tr>
<td>B</td>
<td>11.58</td>
<td>COMMENT</td>
</tr>
<tr>
<td>B</td>
<td>11.59</td>
<td>DELETE</td>
</tr>
<tr>
<td>B</td>
<td>11.60</td>
<td>EDIT</td>
</tr>
<tr>
<td>C</td>
<td>11.60</td>
<td>EDIT</td>
</tr>
<tr>
<td>C</td>
<td>11.60</td>
<td>COMMENT</td>
</tr>
<tr>
<td>C</td>
<td>11.60</td>
<td>POST</td>
</tr>

</table>




Answer
SELECT from_unixtime(logDate/1000) as logDate, userId 
FROM UserLogs UL1
INNER JOIN (SELECT userID, from_UnixTime(logDate/1000) logDate, count(1) cnt 
            FROM userLogs 
            GROUP BY  userID, from_UnixTime(logDate/1000)
            HAVING count(1)>1) UL2
  on UL1.userID = UL2.UserID 
 and UL1.LogDate = from_unixtime(UL1.logDate/1000)
WHERE logDate > subdate(now(), INTERVAL 1 day ) 

What this does is generate a subset of the users days for users having more than 1 entry. it then joins back to the base set to return all 8 records for users on those days; excluding the users having only a count of 1.

Instead of a join you could use an exists statement which may be slightly faster.