显示基于百分比的Sql结果

Question

Good Day Guys! I am trying to get a desired result in SQL.

I have a Table like this

Products | Colors
******************
Bag      | Blue
Shoes    | Blue
Shirt    | Yellow
Sandal   | Yellow
Phone    | Yellow
Cotton   | Blue
Paper    | Blue
Biro     | Blue
Track    | Yellow
Pouch    | Yellow
Cap      | Yellow
Hat      | Yellow
Pants    | Blue
Bucket   | Blue
Belt     | Blue
Car      | Yellow
Socks    | Yellow

The rows above is 20.

What I want to achieve is to display 10 results at random:

where: 3 rows (30% of the result) are blue and 7 rows (70% of the result) are yellow.

Thank you. I apologize for not asking with details.

Answer 1

Here's a method that almost does what you want. To achieve your exact specification in pure SQL is quite a lot more involved...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Products VARCHAR(20) NOT NULL
,Colors VARCHAR(20) NOT NULL
,PRIMARY KEY(products,colors)
);

INSERT INTO my_table VALUES
('Bag','Blue'),
('Shoes','Blue'),
('Shirt','Yellow'),
('Sandal','Yellow'),
('Phone','Yellow'),
('Cotton','Blue'),
('Paper','Blue'),
('Biro','Blue'),
('Track','Yellow'),
('Pouch','Yellow'),
('Cap','Yellow'),
('Hat','Yellow'),
('Pants','Blue'),
('Bucket','Blue'),
('Belt','Blue'),
('Car','Yellow'),
('Socks','Yellow');

SELECT * FROM my_table ORDER BY CASE WHEN colors = 'blue' THEN RAND()*3 ELSE RAND()*7 END DESC LIMIT 10;
+----------+--------+
| Products | Colors |
+----------+--------+
| Socks    | Yellow |
| Shirt    | Yellow |
| Hat      | Yellow |
| Track    | Yellow |
| Cap      | Yellow |
| Sandal   | Yellow |
| Car      | Yellow |
| Pants    | Blue   |
| Shoes    | Blue   |
| Belt     | Blue   |
+----------+--------+

SELECT * FROM my_table ORDER BY CASE WHEN colors = 'blue' THEN RAND()*3 ELSE RAND()*7 END DESC LIMIT 10;
+----------+--------+
| Products | Colors |
+----------+--------+
| Track    | Yellow |
| Phone    | Yellow |
| Hat      | Yellow |
| Socks    | Yellow |
| Cap      | Yellow |
| Sandal   | Yellow |
| Shoes    | Blue   |
| Biro     | Blue   |
| Pouch    | Yellow |
| Bucket   | Blue   |
+----------+--------+

SELECT * FROM my_table ORDER BY CASE WHEN colors = 'blue' THEN RAND()*3 ELSE RAND()*7 END DESC LIMIT 10;
+----------+--------+
| Products | Colors |
+----------+--------+
| Sandal   | Yellow |
| Hat      | Yellow |
| Pouch    | Yellow |
| Socks    | Yellow |
| Cap      | Yellow |
| Bucket   | Blue   |
| Paper    | Blue   |
| Phone    | Yellow |
| Track    | Yellow |
| Shirt    | Yellow |
+----------+--------+