A_id | B_id | L | M | N |
---|---|---|---|---|
1 | 1 | 1 | 1 | 0 |
1 | 2 | 4 | 3 | 2 |
1 | 3 | 2 | 2 | 1 |
2 | 4 | 2 | 2 | 1 |
2 | 5 | 3 | 2 | 1 |
2 | 6 | 1 | 1 | 0 |
3 | 7 | 4 | 3 | 1 |
3 | 8 | 1 | 1 | 0 |
4 | 9 | 4 | 4 | 2 |
Suppose P = 2, Q = 3. I would like to find the A_id such that the following are satisfied:
- the sum of the P greatest L records for A_id >= P+Q
- the sum of the P greatest M records for A_id >= P
- the sum of the P greatest N records for A_id >= Q
I have attempted to solve this and see I will need to use a combination of GROUP BY, ORDER BY, LIMIT and SUM but there is so much going on that I am just lost in how to implement this in SQL. It is beyond my current capability.
Please could someone explain to me how to write a query like this for MySQL.
2
Answers
Some thing like this
Assuming MySQL ≥ 8, you could use a CTE to add row numbering and then conditional aggregation based on the row numbering:
Based on your sample data the output is:
Here’s a db<>fiddle.