gpt4 book ai didi

mysql - 如何获取最新记录

转载 作者:行者123 更新时间:2023-11-29 02:41:49 25 4
gpt4 key购买 nike

我有一个看起来像这样的表:

+--------+-------------+----------+---------+
| PK(id) | FK(user_id) | ctime | comment |
+--------+-------------+----------+---------+
| 1 | 1 | 20170101 | "Haha" |
+--------+-------------+----------+---------+
| 2 | 2 | 20170102 | "Nope" |
+--------+-------------+----------+---------+
| 3 | 2 | 20170104 | "Geez" |
+--------+-------------+----------+---------+
| 4 | 1 | 20170110 | "Gone" |
+--------+-------------+----------+---------+

我希望检索每个 FK(user_id) 的最新记录,如下所示:

+--------+-------------+----------+---------+
| PK(id) | FK(user_id) | ctime | comment |
+--------+-------------+----------+---------+
| 3 | 2 | 20170104 | "Geez" |
+--------+-------------+----------+---------+
| 4 | 1 | 20170110 | "Gone" |
+--------+-------------+----------+---------+

所以我尝试了 SELECT DISTINCT T.* FROM my_table AS TSELECT DISTINCT T.user_id FROM my_table AS T;但是,它们不起作用!

我试过 GROUP BY 语句,如下所示,

SELECT T.* FROM my_table AS T GROUP BY `user_id` DESC

它确实如我所料完美地工作。所以我开始研究如何将 SQL 翻译成 Django!

首先,我尝试使用 RawSQL:

from django.db.models.expressions import RawSQL

def _select_latest_rows(model_class, target_column_name, query_set=None):
query_set = query_set or model_class.objects
table_name = model_class._meta.db_table
raw_sql = '''
SELECT * FROM %s GROUP BY %s
'''
return query_set.annotate(val=RawSQL(raw_sql, (table_name, target_column_name,)))

每当我调用此函数时,Django 都会弹出以下错误。

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax
blah blah blah...

然后我检查了我的查询字符串:

print(_select_latest_rows(model_class, target_column_name, query_set).query)

它返回:

SELECT T.`id`, T.`user_id`, T.`ctime`, T.`comment`, (SELECT * FROM my_table GROUP BY user_id) AS `val` FROM my_table

好吧,我不得不说我对 SQL 语法不是很熟悉,到目前为止我还没有弄清楚这个查询字符串中的错误所在:(

我必须在模型的管理器实例中调用 raw() 函数,如 this answer 中所建议的那样?

model_class.objects.raw('SELECT T.* FROM my_table AS T GROUP BY `user_id` DESC LIMIT 10 OFFSET 0')

或者也许我不应该在开始时使用 GROUP BY 语句?

最佳答案

最简单的方法是:

select * 
from my_table t
where ctime = (select max(ctime) from my_table where user_id = t.user_id);

但是,如果 user_id 有多个相同的日期,您也可以在内部查询中使用 limit 子句

select * 
from my_table t
where id = (select id
from my_table
where user_id = t.user_id
order by ctime desc
LIMIT 1);

关于mysql - 如何获取最新记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50219792/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com