限制 GROUP_CONCAT() 或 INNER JOIN 的结果

Question

我已经仔细阅读了其他讨论group_concat()和inner join限制的帖子，但没有找到我的答案，所以我猜我会继续问：

我正在开发一个现有的照片社区网站。我想检索在给定日期（今天）生日的成员，然后检索每个成员的5张最受欢迎的照片。但我也只想要10个“最喜欢”的生日成员（即收藏次数最高的成员）。这是我的代码：

SELECT users.user_id, users.user_name, 
       GROUP_CONCAT(CONVERT(photos.photo_id,char(32)) 
                    ORDER BY photos.average_rate) as photo_ids
FROM users 
INNER JOIN photos ON photos.user_id=users.user_id
WHERE users.day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
  AND users.photo_count>0 
GROUP BY users.user_id
ORDER BY users.favorite_count DESC, users.photo_count DESC LIMIT 0,10

这个功能实现了我想要的，但是我无法将 photo_id 的数量限制为5。这是一个问题，因为输出将作为JSON发送到应用程序，有些成员已经上传了超过20,000张照片，导致输出字符串不可接受地长。唯一看起来适用于我的“解决方案”是设置服务器变量group_concat_max_len为某个合理的值，该值至少可以保存5个id，但这非常不稳定。是否有方法可以使用单个查询每个用户返回正好5个photo_id？还是说我需要在PHP中循环执行？我不一定需要以逗号分隔的方式返回photo_ids，如果使用内部连接更可行，则完全可以放弃group_concat()方法。但即使在那里，我也不知道如何将结果限制为5。

- indorock

---

请查看右侧的“相关”或[greatest-n-per-group]标签下的问题。 - ypercubeᵀᴹ

我找了一圈，但是我找不到适合我特定情况的答案……或者我的SQL技术水平还不够好，无法为我的情况实现它（后面那种可能性更大）。 - indorock

Answer 1

这些高级功能是我喜欢MySQL的原因 :)

SELECT user_id, user_name, 
    GROUP_CONCAT(CONVERT(photo_id, char(32)) ORDER BY photos.average_rate) as photo_ids
FROM (  SELECT user_id, user_name, photo_id, favorite_count, photo_count, 
            (case when @user_id = user_id then @rownum := @rownum + 1 else CONCAT(@rownum := 1, @user_id := user_id) end) AS dummy_val
        FROM (  SELECT users.user_id, users.user_name, users.favorite_count, users.photo_count, photos.photo_id
                FROM users 
                INNER JOIN photos
                ON photos.user_id=users.user_id
                WHERE users.day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
                    AND users.photo_count > 0 
                ORDER BY users.id ASC, photos.average_rate ASC
             ) AS h, 
             (  @rownum := NULL, 
                @user_id := NULL
             ) AS vars
        HAVING rownum <= 5) AS h2
GROUP BY user_id
ORDER BY favorite_count DESC, photo_count DESC LIMIT 0, 10

基本上，我获取所有行，并且丢弃在计算的行号中为6或更高的所有照片。

- Robin Castlin

Answer 2

SELECT u.user_id
     , u.user_name 
     , GROUP_CONCAT(p.photo_id ORDER BY p.average_rate) AS photo_ids
FROM 
    ( SELECT user_id
           , user_name
           , favorite_count 
           , photo_count
      FROM users
      WHERE day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
        AND photo_count > 0 
      ORDER BY favorite_count DESC
             , photo_count DESC 
      LIMIT 10
    ) AS u
  INNER JOIN
    photos AS p
      ON  p.user_id = u10.user_id
      AND p.average_rate >= 
          ( SELECT pp.average_rate 
            FROM photos AS pp
            WHERE pp.user_id = u10.user_id
            ORDER BY pp.average_rate DESC
            LIMIT 1 OFFSET 4
          ) 
GROUP BY u.user_id
ORDER BY u.favorite_count DESC
       , u.photo_count DESC 

- ypercubeᵀᴹ