mysql - 按多列创建从最小金额到最大金额的排名

Question

目前，我正在使用 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    |
+-----------+----------+----------+--------+---------+---------+------+

请给我解决这个问题的提示。

非常感谢。

Answer 1

尝试重新排列您的 Order by 语句，使您的“金额”成为 order by 中的第二个值。这将使您更接近您想要的。

按 client_id、金额、账户、类别、t_year、t_month 排序

您列出列的顺序将决定它们的排序顺序。

更多信息:https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html