如何将查询结果限制在MySQL中结果总数的百分比?
问题描述:
下面的mysql问题只返回10个第一行。我怎么能限制他们到10%?如何将查询结果限制在MySQL中结果总数的百分比?
SELECT page,
poso,
diff
FROM (SELECT page,
Count(*) AS poso,
(Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day)))
diff
FROM `behaviour`
WHERE Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day)
GROUP BY page
ORDER BY (Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day))
) DESC
LIMIT 10) AS u
ORDER BY diff DESC
答
从the answer to the duplicate question改编:
SELECT page,
poso,
diff
FROM (
SELECT *,
@counter := @counter + 1 AS counter
FROM (select @counter:=0) AS initvar,
(SELECT page,
Count(*) AS poso,
(Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day)))
diff
FROM `behaviour`
WHERE Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day)
GROUP BY page
ORDER BY (Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day))
) DESC) AS u
) AS v
WHERE counter <= 10/100 * @counter
ORDER BY diff DESC;
+1
谢谢!我已经接受了你的答案,我将在14小时内给予答复 – EnexoOnoma
的可能的复制[MySQL的:限制由记录量的百分比(http://*.com/questions/5615172/mysql-limit-by-a-of-the-of-the-records) –