如何在MySqlPHP中拆分结果

Question

我在使用MySql拆分结果时遇到问题。我还是MySql的新手。

我在MySqlproducts_table中有一个这样的表

Id | Product | OWNER
---------------------
1  | Bag     | Admin
2  | Shoes   | Admin
3  | Shirts  | User
4  | Purse   | Admin
5  | Shoes   | User
6  | Sandals | User
7  | Watch   | Admin
8  | Jean    | User
9  | Slippers| Admin
10 | Laptop  | User
11 | Phones  | User
12 | Piano   | Admin
13 | Keyboard| User
14 | Template| Admin
15 | Desktop | Admin

我想要一个最多能产生10个结果的结果，至少有4个产品来自用户，6个产品来自管理员，按随机顺序

所以结果会是这样的。

1  | Bag     | Admin
4  | Purse   | Admin
11 | Phones  | User
14 | Template| Admin
3  | Shirts  | User
9  | Slippers| Admin
5  | Shoes   | User
8  | Jean    | User
7  | Watch   | Admin
12 | Piano   | Admin

事先非常感谢。

Answer 1

您可以在这里使用UNION ALL，以在两个不同的Select查询中获得结果，这是由于两个不同Where条件：

(
SELECT Id, Product, Owner 
FROM products_table 
WHERE Owner = 'User'
ORDER BY RAND() 
LIMIT 4   -- get random 4 rows for 'User'
)
UNION ALL
(
SELECT Id, Product, Owner 
FROM products_table 
WHERE Owner = 'Admin'
ORDER BY RAND() 
LIMIT 6   -- get random 6 rows for 'Admin'
)
ORDER BY RAND() -- eventually random sorting again

---

如果您可以升级到MySQL的最新版本(8.0.2及以上(，我们可以使用Window函数而不是Union。我们将在Owner的特定分区内确定随机化的Row_number()。然后，我们可以将此结果集用作派生表，并只考虑用户的4行和管理员的6行

SELECT dt.* 
FROM 
( SELECT Id, 
Product, 
Owner, 
ROW_NUMBER() OVER (PARTITION BY Owner 
ORDER BY RAND()) AS row_num 
) AS dt 
WHERE (dt.Owner = 'User' AND dt.row_num <= 4) OR 
(dt.Owner = 'Admin' AND dt.row_num <= 6) 
ORDER BY RAND()