特定列值的MYSQL限制出现次数 [英] MYSQL Limit Occurences of a particular column value

问题描述

从数据库中提取一些优惠券.每个优惠券都有一个merchantid列，其中也包含该优惠券所属的商家的ID.

Pulling some coupons from a database. Each coupon has a merchantid column that contains the id for the merchant for which the coupon belongs too.

我正在尝试构建一个查询，其中提取5张优惠券，但我只希望每个merchantid 1张优惠券.我不希望多个具有相同merchantid的优惠券.

I'm trying to construct a query that pulls 5 coupons, but I only want 1 coupon per merchantid. I don't want multiple coupons with the same merchantid.

推荐答案

您可以使用

SELECT * FROM coupons GROUP BY merchantid LIMIT 0,5;

它将起作用，因为

MySQL扩展了GROUP BY的使用，以便选择列表可以引用未在GROUP BY子句中命名的非聚合列(如果您不希望MySQL决定保留哪个merchantid，则可以添加条件 (在下面的示例中-使用最高的点击次数来保持商家点击次数)

If you don't want MySQL to decide which merchantid to keep, you can add your condition (in example below - keep merchant with highest number of clicks) using subquery:

已修复:

SELECT c1.* 
FROM coupons c1 JOIN (
    SELECT t.merchantid, MAX(t.numberofclicks) maxnumberofclicks
    FROM coupons t GROUP BY t.merchantid
) c2 ON c1.merchantid = c2.merchantid AND c1.numberofclicks = c2.maxnumberofclicks 
LIMIT 0,5;

还有一种方法(更简洁，在大型数据集上可能更快)，可以为猫皮:

And one more (more concise and probably faster on large datasets) way to skin a cat:

SELECT c1.*
FROM coupons c1 JOIN coupons c2 ON c1.merchantid = c2.merchantid
GROUP BY c1.merchantid, c1.numberofclicks
HAVING c1.numberofclicks = MAX(c2.numberofclicks)
LIMIT 0,5;

如果您希望获得5张优惠券，以使其获得最高的点击次数，请在LIMIT 0,5之前添加ORDER BY c1.numberofclicks DESC.

If you want 5 coupons with overall highest number of clicks, add ORDER BY c1.numberofclicks DESC before LIMIT 0,5.

这篇关于特定列值的MYSQL限制出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！