mysql - 为每个 IN id 选择查询和限制

Question

我有 contacts 表，其中包含 id|city_id|category_id|email 字段。
基本查询是

SELECT id
     , email
  FROM contacts 
 WHERE city_id IN (".implode(',', $arrCity).") 
   AND category_id IN (".implode(',', $arrCategory).")

但我需要设置一个限制:城市中每个类别 10 行。可能吗？

Answer 1

这很棘手。在 MySQL 中，最简单的方法是使用变量:

SELECT id, email
FROM (SELECT c.*,
             (@rn := IF(@cc = CONCAT_WS(':', city_id, category_id), @rn + 1,
                        IF(@cc := CONCAT_WS(':', city_id, category_id), 1, 1)
                       )
             ) as rn
      FROM (SELECT c.*
            FROM contacts c
            WHERE city_id IN (".implode(',', $arrCity).") AND 
                  category_id IN (".implode(',', $arrCategory).")
            ORDER BY city_id, category_id
           ) c CROSS JOIN
           (SELECT @cc := '', @rn := 0) params
     ) c
WHERE rn <= 10;

这会枚举每个城市/类别组合内的行。外部 WHERE 将每个组合限制为 10 个。