目前,我正在使用MySQL 5.6.30,我需要您的帮助。
这是表中名称temp_work的数据
+----+-----------+----------+----------+--------+---------+-------+---------+
| id | client_id | account | category | t_year | t_month | t_day | amount |
+----+-----------+----------+----------+--------+---------+-------+---------+
| 1 | 100 | Saving | deposit | 2016 | 12 | 14 | 100.84 |
| 2 | 100 | Checking | withdraw | 2016 | 12 | 15 | 300.24 |
| 3 | 100 | Checking | deposit | 2016 | 12 | 29 | 60.00 |
| 4 | 101 | Saving | Withdraw | 2016 | 12 | 29 | 245.16 |
| 5 | 100 | Saving | Withdraw | 2016 | 12 | 30 | 2200.00 |
| 6 | 100 | Checking | Withdraw | 2016 | 12 | 30 | 2372.16 |
| 7 | 100 | Saving | deposit | 2016 | 12 | 30 | 4327.00 |
| 8 | 101 | Checking | Withdraw | 2017 | 1 | 3 | 80.00 |
| 9 | 101 | Checking | Withdraw | 2017 | 1 | 3 | 1033.45 |
| 10 | 100 | Saving | Withdraw | 2017 | 1 | 3 | 1339.16 |
| 11 | 100 | Checking | deposit | 2017 | 1 | 4 | 140.00 |
| 12 | 100 | Checking | Withdraw | 2017 | 1 | 4 | 216.73 |
| 13 | 101 | Checking | Withdraw | 2017 | 1 | 4 | 1261.72 |
+----+-----------+----------+----------+--------+---------+-------+---------+
我需要按client_id、帐户、类别、t_year t_month从最低金额到最大金额组进行排名。
所以,这是我所期望的或类似的
+-----------+----------+----------+--------+---------+---------+------+
| client_id | account | category | t_year | t_month | amount | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100 | Checking | deposit | 2016 | 12 | 60.00 | 1 |
| 100 | Checking | deposit | 2017 | 1 | 140.00 | 1 |
| 100 | Checking | withdraw | 2016 | 12 | 300.24 | 1 |
| 100 | Checking | withdraw | 2016 | 12 | 2327.16 | 2 |
| 100 | Checking | Withdraw | 2017 | 1 | 216.73 | 1 |
| 100 | Saving | deposit | 2016 | 12 | 100.84 | 1 |
| 100 | Saving | deposit | 2016 | 12 | 4327.00 | 2 |
| 100 | Saving | Withdraw | 2016 | 12 | 2200.00 | 1 |
| 100 | Saving | Withdraw | 2017 | 1 | 1339.16 | 1 |
| 101 | Checking | Withdraw | 2017 | 1 | 80.00 | 1 |
| 101 | Checking | Withdraw | 2017 | 1 | 1033.45 | 2 |
| 101 | Checking | Withdraw | 2017 | 1 | 1261.72 | 3 |
| 101 | Saving | Withdraw | 2016 | 12 | 245.16 | 1 |
+-----------+----------+----------+--------+---------+---------+------+
这是我的第一次尝试:
Select
tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
,@rownum = case when @tmonth <> tw1.t_month then 0 else @rownum + 1 end as ranking
,@tmonth := t_month as cmonth
From
( Select @rownum := 0 from dual ) as r,
( Select @tmonth := 0 from dual ) as m,
( Select client_id, account, category, t_year, t_month, amount
From temp_work as tw
Order by client_id, account, category, t_year, t_month, amount) as tw1
第二次尝试:
Select tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
,@rownum = case when concat(@tyear, '-', @tmonth) <> concat(t_year, '-', t_month) then 0 else @rownum + 1 end as rank
,@tyear := t_year as cyear
,@tmonth := t_month as cmonth
From
( Select @rownum := 0 ) as r,
( Select @tyear := 0 ) as y,
( Select @tmonth := 0 ) as m,
(
Select client_id, account, category, t_year, t_month, amount
From temp_work
Order by tw.client_id, tw.account, tw.category, t_year, t_month, tw.amount
) as tw1
但是,上面的查询给了我相同的结果,如下所示。
+-----------+----------+----------+--------+---------+---------+------+
| client_id | account | category | t_year | t_month | amount | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100 | Checking | deposit | 2016 | 12 | 60.00 | 1 |
| 100 | Checking | deposit | 2017 | 1 | 140.00 | 1 |
| 100 | Checking | withdraw | 2016 | 12 | 300.24 | 1 |
| 100 | Checking | withdraw | 2016 | 12 | 2327.16 | 0 |
| 100 | Checking | Withdraw | 2017 | 1 | 216.73 | 1 |
| 100 | Saving | deposit | 2016 | 12 | 100.84 | 1 |
| 100 | Saving | deposit | 2016 | 12 | 4327.00 | 0 |
| 100 | Saving | Withdraw | 2016 | 12 | 2200.00 | 1 |
| 100 | Saving | Withdraw | 2017 | 1 | 1339.16 | 1 |
| 101 | Checking | Withdraw | 2017 | 1 | 80.00 | 1 |
| 101 | Checking | Withdraw | 2017 | 1 | 1033.45 | 0 |
| 101 | Checking | Withdraw | 2017 | 1 | 1261.72 | 0 |
| 101 | Saving | Withdraw | 2016 | 12 | 245.16 | 1 |
+-----------+----------+----------+--------+---------+---------+------+
请给我解决这个问题的提示。
谢谢。
尝试重新排列您的订单依据语句,使您的"金额"成为订单依据中的第二个值。这将使您更接近您想要的。
Order by client_id, amount, account, category, t_year, t_month
列出列的顺序将决定列的排序顺序。
更多信息: https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html
考虑一个用于排名的相关计数聚合子查询:
SELECT t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount,
(SELECT COUNT(*) FROM temp_work sub
WHERE sub.amount <= t.amount
AND sub.client_id = t.client_id
AND sub.account = t.account
AND sub.t_year = t.t_year
AND sub.t_month = t.t_Month) AS rank
FROM temp_work t
ORDER BY t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount