我有兩張桌子，一張是獎品桌，一張是結果桌
獎品表
pm_id pm_fromrank pm_torank pm_prize pool_id
1 1 1 1000 72
2 2 5 500 72
3 6 10 270 72
結果表有 3 種型別的排名案例 1st CASE
rs_id rs_userid rs_rank rs_poolid
1 131 1 72
2 132 1 72
3 133 2 72
4 134 3 72
5 135 4 72
6 136 5 72
7 137 6 72
8 138 6 72
9 139 7 72
10 140 8 72
11 141 9 72
12 142 10 72
第二個案例
rs_id rs_userid rs_rank rs_poolid
1 131 1 72
2 132 2 72
3 133 3 72
4 134 4 72
5 135 5 72
6 136 6 72
7 137 7 72
8 138 8 72
9 139 9 72
10 140 10 72
11 141 11 72
12 142 12 72
第三種情況
rs_id rs_userid rs_rank rs_poolid
1 131 1 72
2 132 2 72
3 133 3 72
4 134 4 72
5 135 5 72
6 136 6 72
7 137 7 72
8 138 8 72
9 139 9 72
10 140 10 72
11 141 10 72
12 142 10 72
now i have to distribute price only first 10 user in ascending order in following ways
1ST CASE PRIZE DISTRIBUTION
rs_id rs_userid rs_rank rs_poolid pricemoney
1 131 1 72 750
2 132 1 72 750
3 133 2 72 500
4 134 3 72 500
5 135 4 72 500
6 136 5 72 270
7 137 6 72 270
8 138 6 72 270
9 139 7 72 270
10 140 8 72 270
11 141 9 72 0
12 142 10 72 0
二等獎發放
rs_id rs_userid rs_rank rs_poolid pricemoney
1 131 1 72 1000
2 132 2 72 500
3 133 3 72 500
4 134 4 72 500
5 135 5 72 500
6 136 6 72 270
7 137 7 72 270
8 138 8 72 270
9 139 9 72 270
10 140 10 72 270
11 141 11 72 0
12 142 12 72 0
三等獎分配
rs_id rs_userid rs_rank rs_poolid pricemoney
1 131 1 72 1000
2 132 2 72 500
3 133 3 72 500
4 134 4 72 500
5 135 5 72 500
6 136 6 72 270
7 137 7 72 270
8 138 8 72 270
9 139 9 72 270
10 140 10 72 90
11 141 10 72 90
12 142 10 72 90
注意：- 獎表和結果表與 POOL_ID 有關系我必須使用 mysql 和 codeignitor 根據排名找到獎品分布
uj5u.com熱心網友回復：
MySQL 8 版本的解決方案：
WITH
cte1 AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY rs_rank, rs_id) rn,
DENSE_RANK() OVER (ORDER BY rs_rank) drnk
FROM result
ORDER BY rs_rank, rs_id LIMIT 10),
cte2 AS (SELECT cte1.drnk, SUM(prize.pm_prize) totalmoney
FROM cte1
JOIN prize ON cte1.rn BETWEEN prize.pm_fromrank AND pm_torank
GROUP BY cte1.drnk),
cte3 AS (SELECT *,
DENSE_RANK() OVER (ORDER BY rs_rank) drnk
FROM result
ORDER BY rs_rank, rs_id),
cte4 AS (SELECT MAX(drnk) max_drnk
FROM cte1),
cte5 AS (SELECT rs_id, rs_rank, COUNT(*) OVER (PARTITION BY rs_rank) rank_count
FROM cte3
JOIN cte4 ON cte3.drnk <= cte4.max_drnk)
SELECT result.*, cte2.totalmoney / cte5.rank_count pricemoney
FROM cte5
JOIN result USING (rs_id)
JOIN cte2 ON result.rs_rank = cte2.drnk
擺弄解釋。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/416833.html
標籤：
上一篇：使用陣列更新SQL中的多個表
下一篇：將三個SQL查詢合二為一