如何使用Django的ORM创建一个临时表以按两个条件对同一列进行排序? [英] How do I create a temporary table to sort the same column by two criteria using Django's ORM?

问题描述

CREATE TEMPORARY TABLE TEMP (SELECT * FROM TABLE ORDER BY COLUMN_A);
SELECT * FROM TEMP ORDER BY COLUMN_B;

我有一个表存储每个城市的最近24小时下午点，我想选择每个城市的最新下午点.所以我使用按日期排序从city_pm顺序中选择*限制the_count_of_city"，然后我想要一个每个城市的最新pm点的排名，因此我在选择之前创建了一个临时表，并按pm点对表进行排序...这是我要完成的工作，我想不出更好的方法因此，我决定创建一个临时表.

I have a table stores every city's last 24 hours pm point, I want to select the latest pm point of every city.So I use "Select * from city_pm order by date desc limit the_count_of_city", then I want to have a rank of the latest pm point of every city, so I use the select before to create a temporary table, and order the table by pm point...this is what I'm attempting to accomplish, I can't figure out a better way so I decide to create a temporary table.

推荐答案

我相信左联接可以在这种情况下提供帮助.您的目标是在最后一个PM点之前对城市进行排序.

I believe left join's may assist in this situation. Your goal is to order cities by the last PM point.

假设city表的主键是city_id，另一个名为city_pm的表保存PM点，并且您有两个模型City和City_Pm ...

Assuming the primary key of your city table is city_id, and a separate table called city_pm holds the PM points, and that you have two models City and City_Pm...

您认为:

cities = City.objects.all()

在您的模型中:

class City(models.Model):
    # fields ...

    def get_latest_pm(self):
        try:
            return City_Pm.objects.filter(city_id=self.pk).order_by("-date")[:1].get()
        except:
            return None

在您的模板中:

{% for city in cities %}
    {{ city.get_latest_pm }}
{% endfor %}

这篇关于如何使用Django的ORM创建一个临时表以按两个条件对同一列进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！