Selecting with a limit, but also a limit on the amount of times a column can have a specific value

I've got the following SQL query. I want to get 5 posts from this query, but I also want to limit it so that I can, at most, get two posts per user. Only two users in this case, would mean a maximum of four posts.

SELECT DISTINCT * 
FROM   posts 
WHERE  (user_id IN (2,1000001) 
       AND NOT 开发者_如何学Pythontrack_id = 34) 
GROUP BY 
       track_id 
ORDER BY 
       id desc LIMIT 5

Add a condition to your WHERE clause in which you count the number of rows for that user with a greater id, and make sure there are only 1 or 0 rows.

 SELECT DISTINCT * 
 FROM   posts P1
 WHERE  user_id IN (2,1000001) AND NOT track_id = 34 AND
        (SELECT COUNT(*) FROM posts P2 
            WHERE P2.user_id = P1.user_id AND P2.id > P1.id AND P2.track_id <> 34)
         <= 1
 GROUP BY 
        track_id 
 ORDER BY 
        id desc LIMIT 5

SELECT sub.* FROM 
  (SELECT p1.* 
   FROM posts p1 
   LEFT JOIN posts p2 ON (p1.user_id = p2.user_id AND p1.id < p2.id)
   LEFT JOIN posts p3 ON (p1.user_id = p3.user_id AND p2.id < p3.id)
   WHERE user_id IN ('2','1000001') 
   AND NOT track_id = '34'
   AND p3.id IS NULL
   ORDER BY user_id) sub
GROUP BY sub.track_id
ORDER BY sub.id DESC
LIMIT 5

The subselect only allows the posts with the top 2 users.id to be selected, this forces only 2 rows to be selected.
It does however force the outcome in a certain way, so you may want to see if this fits your use case.