mysql - 从表中获取用户的分组记录

Question

我正在尝试从用户持有表中获取一些数据。我的用户持有表如下:

+----+-------------+--------------+----------------------------+------------+--------+
| id | Qty_holding | Qty_reserved | created                    | tokenid_id | uid_id |
+----+-------------+--------------+----------------------------+------------+--------+
|  1 |          10 |            0 | 2018-01-18 10:52:14.957027 |          1 |      1 |
|  2 |          20 |            0 | 2018-01-18 11:20:08.205006 |          8 |      1 |
|  3 |         110 |            0 | 2018-01-18 11:20:21.496318 |         14 |      1 |
|  4 |          10 |            0 | 2018-01-23 14:26:49.124607 |          1 |      2 |
|  5 |           3 |            0 | 2018-01-23 15:00:26.876623 |         11 |      2 |
|  6 |           7 |            0 | 2018-01-23 15:08:41.887240 |         11 |      2 |
|  7 |          11 |            0 | 2018-01-23 15:22:48.424224 |         11 |      2 |
|  8 |          15 |            0 | 2018-01-23 15:24:03.419907 |         11 |      2 |
|  9 |          19 |            0 | 2018-01-23 15:24:26.531141 |         11 |      2 |
| 10 |          23 |            0 | 2018-01-23 15:27:11.549538 |         11 |      2 |
| 11 |          27 |            0 | 2018-01-23 15:27:24.162944 |         11 |      2 |
| 12 |   7.7909428 |   0.11459088 | 2018-01-23 15:27:24.168643 |          1 |      2 |
| 13 |           3 |            0 | 2018-01-23 15:36:51.412340 |         14 |      2 |
| 14 |   7.5585988 |   0.11459088 | 2018-01-23 15:36:51.417177 |          1 |      2 |
| 15 |           6 |            0 | 2018-01-24 08:43:46.635069 |         14 |      2 |
| 16 |   7.3262548 |   0.11459088 | 2018-01-24 08:43:46.639984 |          1 |      2 |
| 17 |           9 |            0 | 2018-01-24 10:09:08.207816 |         14 |      2 |
| 18 |   7.0939108 |   0.11459088 | 2018-01-24 10:09:08.212842 |          1 |      2 |
| 19 |           6 |            3 | 2018-01-24 13:43:08.929586 |         14 |      2 |
| 20 |           3 |            6 | 2018-01-24 14:49:56.960112 |         14 |      2 |
| 21 |           0 |            9 | 2018-01-24 14:50:33.423671 |         14 |      2 |
| 22 |          30 |            9 | 2018-01-24 14:51:14.865453 |         14 |      2 |
| 23 |   4.7704708 |   0.11459088 | 2018-01-24 14:51:14.870256 |          1 |      2 |
| 24 |          27 |           12 | 2018-01-24 14:56:56.914009 |         14 |      2 |
| 25 |          24 |           15 | 2018-01-24 14:57:56.475939 |         14 |      2 |
| 26 |          21 |           15 | 2018-01-24 14:58:06.750903 |         14 |      2 |
| 27 |          18 |           15 | 2018-01-24 15:02:43.203878 |         14 |      2 |
| 28 |   4.7705074 |   0.11459088 | 2018-01-24 15:02:43.224901 |          1 |      2 |
| 29 |          24 |            0 | 2018-01-24 15:03:40.421943 |         11 |      2 |
| 30 |   4.9535074 |   0.11459088 | 2018-01-24 15:03:40.441552 |          1 |      2 |
| 31 |           1 |            0 | 2018-01-26 10:35:33.173801 |         18 |      2 |
+----+-------------+--------------+----------------------------+------------+--------+

我必须找到每个用户对每个代币的最新持有量。我正在尝试:

mysql> select uid_id,tokenid_id,Qty_holding from accounts_userholding group by uid_id,tokenid_id order by created desc;

结果如下:

+--------+------------+-------------+
| uid_id | tokenid_id | Qty_holding |
+--------+------------+-------------+
|      2 |         18 |           1 |
|      2 |         14 |           3 |
|      2 |         11 |           3 |
|      2 |          1 |          10 |
|      1 |         14 |         110 |
|      1 |          8 |          20 |
|      1 |          1 |          10 |
+--------+------------+-------------+

此结果中的问题是 Qty_holding 不是最新的。此外，我正在尝试编写等效的 Django 查询，如下所示:

  UserHolding.objects.values('tokenid_id','uid_id','Qty_holding').order_by().annotate(Count('tokenid_id'),Count('uid_id'))

我们将不胜感激。

Answer 1

你可以用子查询来做

select t1.*
from accounts_userholding t1
join
(
    select uid_id, tokenid_id, max(created) as max_created 
    from accounts_userholding 
    group by uid_id, tokenid_id 
) t2 on t1.uid_id = t2.uid_id
   and t1.tokenid_id = t2.tokenid_id
   and t1.created = t2.max_created