一、问题
上一篇 mysql 取并列前10,不去重
第二种解决办法SQL查询语句
SELECT b.assign_name,b.introduce_amount
FROM daily_assign_bug b
WHERE b.introduce_amount IN
(
SELECT introduce_amount
FROM daily_assign_bug
GROUP BY introduce_amount
ORDER BY introduce_amount DESC
LIMIT 10
) ORDER BY b.introduce_amount DESC
;
执行时,报错:[Err] 1235-此版本的MySQL尚不支持“ LIMIT&IN / ALL / ANY / SOME子查询”
二、解决
从报错可以看出是MYSQL版本不支持,所以第一种解决办法是升级MYSQL版本。
但是,更新mysql版本成本较高,可能会引入更多问题
所以,第二种办法是修改查询SQL,让 limit 不在 in 子查询中
1、联表:将查询前10引入数量查询结果作为一张关联表与原表关联
SELECT b.assign_name,b.introduce_amount
FROM daily_assign_bug b,
(
SELECT introduce_amount
FROM daily_assign_bug
GROUP BY introduce_amount
ORDER BY introduce_amount DESC
LIMIT 10
) a
WHERE a.introduce_amount=b.introduce_amount
ORDER BY b.introduce_amount DESC;
2、嵌套:在 in 子查询中,再嵌套一层查询
SELECT b.assign_name,b.introduce_amount
FROM daily_assign_bug b
WHERE b.introduce_amount IN
(
SELECT introduce_amount
FROM
(
SELECT introduce_amount
FROM daily_assign_bug
GROUP BY introduce_amount
ORDER BY introduce_amount DESC
LIMIT 10
) a
);
结果都是一样的