我有一张这样的table:
+--------+-------------+----------+---------+
| 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 T
以及 SELECT DISTINCT T.user_id FROM my_table AS T
; 然而,他们不会工作!
我试过一组一组的陈述,如下所示,
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()
在我的模型的manager示例中的函数,正如这个答案中所建议的那样?
model_class.objects.raw('SELECT T.* FROM my_table AS T GROUP BY `user_id` DESC LIMIT 10 OFFSET 0')
或者我不应该在一开始就用groupby语句?
2条答案
按热度按时间ssgvzors1#
oracle/postgresql:
尝试使用下面的sql,它将满足您的要求。
zaq34kh62#
最简单的方法是:
但是,您也可以使用
limit
如果有多个相同的查询日期,则在内部查询中使用子句user_id
```select *
from my_table t
where id = (select id
from my_table
where user_id = t.user_id
order by ctime desc
LIMIT 1);