Mysql - 按百分比限制

MySQL - 按百分比限制

sql
SELECT *
FROM (
  SELECT *,
         @total := (SELECT COUNT(*) FROM products) AS total,
         @percentage := 0.1 AS percentage
  FROM products
  ORDER BY sales DESC
) AS subquery
WHERE (@percentage * @total) >= (SELECT COUNT(*) FROM products WHERE sales >= subquery.sales)

sql
-- 创建表products并插入测试数据
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  price DECIMAL(10, 2),
  sales INT
);
INSERT INTO products (name, price, sales)
VALUES ('商品A', 10.99, 100),
       ('商品B', 15.99, 200),
       ('商品C', 20.99, 300),
       ('商品D', 25.99, 400),
       ('商品E', 30.99, 500),
       ('商品F', 35.99, 600),
       ('商品G', 40.99, 700),
       ('商品H', 45.99, 800),
       ('商品I', 50.99, 900),
       ('商品J', 55.99, 1000);
-- 查询销量排名前10%的商品
SELECT *
FROM (
  SELECT *,
         @total := (SELECT COUNT(*) FROM products) AS total,
         @percentage := 0.1 AS percentage
  FROM products
  ORDER BY sales DESC
) AS subquery
WHERE (@percentage * @total) >= (SELECT COUNT(*) FROM products WHERE sales >= subquery.sales);