mysqli:为IN ID中的每一个选择查询和限制
我有contacts
具有id | city_id | category_id |电子邮件字段的表。
基本的查询是mysqli:为IN ID中的每一个选择查询和限制
"
SELECT id
, email
FROM contacts
WHERE city_id IN (".implode(',', $arrCity).")
AND category_id IN (".implode(',', $arrCategory).")
"
但我需要设置一个限制:10排在城市的各个类别。可能吗?
这很棘手。在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。
现在我在'(@rn = IF(@cc = CONCAT_WS(':',city_id,category_id),@rn + 1''字段列表'中有'未知列'city_id'这是奇怪的 – fiter
@Fiter。。。'city_id'在你原来的查询中。 –
为什么会这样downvoted? –
请参阅https://meta.*.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple- sql-query – Strawberry