SELECT *, SUM( cardtype.price - cardtype.cost ) AS profit
FROM user
LEFT OUTER JOIN card ON ( user.id = card.buyer_id )
LEFT OUTER JOIN cardtype ON ( card.cardtype_id = cardtype.id )
GROUP BY user.id
ORDER BY profit DESC
我试过这个:
User.objects.extra(select=dict(profit='SUM(cardtype.price-cardtype.cost)')).annotate(sum=Sum('card__cardtype__price')).order_by('-profit')
但 Django 自动将 SUM( cardtype.price ) 添加到 GROUP BY 子句中，SQL 不运行。
这可以在没有原始 SQL 的情况下完成吗？
提供模型，别管这些汉字:)
class User(models.Model):
class Meta:
verbose_name = "用户"
verbose_name_plural = "用户"
ordering = ['-regtime']
user_status= (
("normal", "正常"),
("deregistered", "注销"),
("locked", "锁定"),
)
name = models.CharField("姓名", max_length=20, db_index=True)
spec_class = models.ForeignKey(SpecClass, verbose_name="专业班级")
idcard = models.CharField("身份证号", max_length=18)
mobileno = models.CharField("手机号", max_length=11)
password = models.CharField("密码", max_length=50) # plain
address = models.CharField("住址", max_length=100)
comment = models.TextField("备注")
certserial = models.CharField("客户证书序列号", max_length=100)
regtime = models.DateTimeField("注册时间", default=datetime.datetime.now)
lastpaytime = models.DateTimeField("上次付款时间", default=datetime.datetime.now)
credit = models.FloatField("信用额度", default=100)
money = models.FloatField("余额", default=0)
use_password = models.BooleanField("使用密码")
use_fetion = models.BooleanField("接收飞信提示")
status = models.CharField("账户状态", choices = user_status, default="normal", max_length=20, db_index=True)
def __unicode__(self):
return self.name
class CardType(models.Model):
class Meta:
verbose_name = "点卡类型"
verbose_name_plural = "点卡类型"
ordering = ['name']
name = models.CharField("类型名称", max_length=20, db_index=True)
note = models.CharField("说明", max_length=100)
offcial = models.BooleanField("官方卡", default=True)
available = models.BooleanField("可用", default=True, db_index=True)
payurl = models.CharField("充值地址", max_length=200)
price = models.FloatField("价格")
cost = models.FloatField("进货价格")
def __unicode__(self):
return u"%s(%.2f元%s)" % (self.name, self.price, u", 平台卡" if not self.offcial else "")
def profit(self):
return self.price - self.cost
profit.short_description = "利润"
class Card(models.Model):
class Meta:
verbose_name = "点卡"
verbose_name_plural = "点卡"
ordering = ['-createtime']
card_status = (
("instock", "未上架"),
("available", "可用"),
("sold", "已购买"),
("invalid", "作废"),
("returned", "退卡"), # sell to the same person !
("reselled", "退卡重新售出"),
)
cardtype = models.ForeignKey(CardType, verbose_name="点卡类型")
serial = models.CharField("卡号", max_length=40)
password = models.CharField("卡密", max_length=20)
status = models.CharField("状态", choices = card_status, default="instock", max_length=20, db_index=True)
createtime = models.DateTimeField("入库时间")
buytime = models.DateTimeField("购买时间", blank=True, null=True)
buyer = models.ForeignKey(User, blank=True, null=True, verbose_name="买家")
def __unicode__(self):
return u'%s[%s]' % (self.cardtype.name, self.serial)
请您参考如下方法:
首先，其中一个外连接对于这种事情来说似乎不是一个好主意。由于您没有提供有关模型的信息，我只能猜测。
您是说您可能不会为每个用户准备一张 CARD？这有点道理。
你是说有些卡没有卡类型？这通常没有意义。你没有提供任何细节。但是，如果卡片没有卡片类型，我敢打赌您要么在应用程序的其他地方遇到了问题，要么您选择了非常糟糕的名称，这些名称对于这些东西的含义没有提供任何线索。您应该修复应用程序的其他部分，以确保每张卡片实际上都有一个卡片类型。或者你应该修改你的名字是有意义的。
很明显，ORM 语句使用内部连接，而您的 SQL 使用外部连接。真正的问题是什么？如何正确进行外连接？ 如果您花时间搜索 [Django] 和 Left Outer Join，您会发现 Raw SQL 是一个糟糕的主意。
或者真正的问题是如何正确求和？从您自己的回答来看，SQL 似乎是错误的，您在计算总和时确实遇到了麻烦。如果是这样，请清理 SQL 以使其正确。
如果外连接是问题的一部分——而不仅仅是视觉噪音——那么你必须对带有总和的外连接执行类似这样的操作。
def user_profit():
for u in User.objects.all():
profit = sum[ t.price - t.cost
for c in u.card_set.all()
for t in c.cardtype_set.all() ]
yield user, profit
在您的 View 函数中，您可以将函数的值提供给模板以呈现报告。由于它是一个生成器，因此不会在内存中创建庞大的列表。如果您需要分页，您可以将生成器提供给分页器，一切都会运行良好。
这通常具有与具有大量外部连接的复杂原始 SQL 查询相当的速度。
如果卡与卡类型的关系确实不是可选的，那么您可以稍微缩短它。您仍然需要考虑外部联接。
def user_profit():
for u in User.objects.all():
profit = sum[ c.cardtype.price - c.cardtype.cost
for c in u.card_set.all() ]
yield user, profit
