如何排列和分组 MySQL 结果 [英] How to arrange and group MySQL results
本文介绍了如何排列和分组 MySQL 结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我不知道如何开始，有人可以帮助我.我有一张这样的桌子:
id |code|移|速度--------------------------1. |AA |早上 |102. |BB |下午 |203. |AA |晚上 |134. |BB |夜 |185. |BB |早上 |156. |AA |下午 |257. |BB |晚上 |158. |AA |夜 |229. |AA |早上 |1010. |BB |下午 |2011. |AA |晚上 |1312. |AA |夜 |1813. |BB |早上 |1514. |BB |下午 |2515. |AA |晚上 |1516. |BB |夜 |2217. |AA |早上 |1018. |BB |下午 |2019. |BB |晚上 |1320. |AA |夜 |1821. |AA |早上 |1522. |BB |下午 |2523. |AA |晚上 |1524. |BB |早上 |1025. |BB |下午 |226. |AA |晚上 |827. |BB |夜 |328. |AA |早上 |529. |BB |下午 |230. |AA |晚上 |131. |BB |夜 |232. |AA |夜 |2我想从班次"列中随机选择 2 个上午、2 个下午、2 个晚上和 2 个晚上.并且所选值的 'rate' 列的总和将小于 100.同样在结果集中，AA 必须出现四次，同样来自代码"列的 BB.
解决方案
这是一个更简单问题的有效结果.
- 1 个上午、1 个下午、1 个晚上和 1 个晚上的轮班"
- 'rate' 之和
- 50
- AA 和 BB(唯一可能的值)必须各自出现两次
(721 个有效结果之一)
I don't know how to start on this can someone please help me. I have a table like this:
id |code| shift | rate
--------------------------
1. | AA | Morning | 10
2. | BB | Afternoon | 20
3. | AA | Evening | 13
4. | BB | Night | 18
5. | BB | Morning | 15
6. | AA | Afternoon | 25
7. | BB | Evening | 15
8. | AA | Night | 22
9. | AA | Morning | 10
10. | BB | Afternoon | 20
11. | AA | Evening | 13
12. | AA | Night | 18
13. | BB | Morning | 15
14. | BB | Afternoon | 25
15. | AA | Evening | 15
16. | BB | Night | 22
17. | AA | Morning | 10
18. | BB | Afternoon | 20
19. | BB | Evening | 13
20. | AA | Night | 18
21. | AA | Morning | 15
22. | BB | Afternoon | 25
23. | AA | Evening | 15
24. | BB | Morning | 10
25. | BB | Afternoon | 2
26. | AA | Evening | 8
27. | BB | Night | 3
28. | AA | Morning | 5
29. | BB | Afternoon | 2
30. | AA | Evening | 1
31. | BB | Night | 2
32. | AA | Night | 2
I want to randomly select 2 morning, 2 afternoon, 2 evening and 2 night from the 'shift' column. And the sum of the 'rate' column of the selected values will be less than 100. Also in the result set AA must appear four times likewise BB from the 'code' column.
解决方案
Here's a valid result for a simpler problem.
- 1 morning, 1 afternoon, 1 evening and 1 night 'shift'
- sum of the 'rate' < 50
- AA and BB (the only possible values) must each appear twice
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,code CHAR(2) NOT NULL
,shift VARCHAR(12) NOT NULL
,rate INT NOT NULL
);
INSERT INTO my_table VALUES
( 1 , 'AA' , 'Morning' , 10),
( 2 , 'BB' , 'Afternoon' , 20),
( 3 , 'AA' , 'Evening' , 13),
( 4 , 'BB' , 'Night' , 18),
( 5 , 'BB' , 'Morning' , 15),
( 6 , 'AA' , 'Afternoon' , 25),
( 7 , 'BB' , 'Evening' , 15),
( 8 , 'AA' , 'Night' , 22),
( 9 , 'AA' , 'Morning' , 10),
(10 , 'BB' , 'Afternoon' , 20),
(11 , 'AA' , 'Evening' , 13),
(12 , 'AA' , 'Night' , 18),
(13 , 'BB' , 'Morning' , 15),
(14 , 'BB' , 'Afternoon' , 25),
(15 , 'AA' , 'Evening' , 15),
(16 , 'BB' , 'Night' , 22),
(17 , 'AA' , 'Morning' , 10),
(18 , 'BB' , 'Afternoon' , 20),
(19 , 'BB' , 'Evening' , 13),
(20 , 'AA' , 'Night' , 18),
(21 , 'AA' , 'Morning' , 15),
(22 , 'BB' , 'Afternoon' , 25),
(23 , 'AA' , 'Evening' , 15),
(24 , 'BB' , 'Morning' , 10),
(25 , 'BB' , 'Afternoon' , 2),
(26 , 'AA' , 'Evening' , 8),
(27 , 'BB' , 'Night' , 3),
(28 , 'AA' , 'Morning' , 5),
(29 , 'BB' , 'Afternoon' , 2),
(30 , 'AA' , 'Evening' , 1),
(31 , 'BB' , 'Night' , 2),
(32 , 'AA' , 'Night' , 2);
SELECT * -- for simplicity. In reality, we would need to name and alias all columns for this result to be usable
FROM my_table morning1
JOIN my_table afternoon1
ON afternoon1.id <> morning1.id
JOIN my_table evening1
ON evening1.id NOT IN(morning1.id,afternoon1.id)
JOIN my_table night1
ON night1.id NOT IN(morning1.id,afternoon1.id,evening1.id)
WHERE morning1.shift = 'morning'
AND afternoon1.shift = 'afternoon'
AND evening1.shift = 'evening'
AND night1.shift = 'night'
AND morning1.rate + afternoon1.rate + evening1.rate + night1.rate < 50
AND LENGTH(REPLACE(CONCAT(morning1.code,afternoon1.code,evening1.code,night1.code),'AA',''))=4
ORDER
BY RAND() LIMIT 1;
+----+------+---------+------+----+------+-----------+------+----+------+---------+------+----+------+-------+------+
| id | code | shift | rate | id | code | shift | rate | id | code | shift | rate | id | code | shift | rate |
+----+------+---------+------+----+------+-----------+------+----+------+---------+------+----+------+-------+------+
| 1 | AA | Morning | 10 | 25 | BB | Afternoon | 2 | 15 | AA | Evening | 15 | 27 | BB | Night | 3 |
+----+------+---------+------+----+------+-----------+------+----+------+---------+------+----+------+-------+------+
(One of 721 valid results)
这篇关于如何排列和分组 MySQL 结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
