我想把两个查询合并成一个:qa和qs。在对列求和时,每个查询本身都会给出正确的结果。当这两个列组合在一起时,求和列的总和不正确。我尝试了几种分组和使用func.sum的方法
一个查询是可用数量:
qa = Alltitles.query.join(Partnames, Partnames.id == Alltitles.partname_id) \
.join(Modelnumbers, Modelnumbers.id == Alltitles.modelnumber_id) \
.add_columns(Modelnumbers.modelnumber,
Partnames.partname,
func.sum(Alltitles.quantityavailable - Alltitles.quantitysold))\
.group_by(Alltitles.modelnumber_id, Alltitles.partname_id)\
.order_by(Modelnumbers.modelnumber, Partnames.partname)
示例输出行:
qa: (<Alltitles 486501>, 'H737Q2', 'Resistor', 6) # correct quantity available
另一个查询是销售数量:
qs = Alltitles.query.join(Partnames, Partnames.id == Alltitles.partname_id) \
.join(Modelnumbers, Modelnumbers.id == Alltitles.modelnumber_id) \
.outerjoin(Allsoldrecords, Allsoldrecords.title_id == Alltitles.id) \
.add_columns(Modelnumbers.modelnumber,
Partnames.partname,
func.sum(Allsoldrecords.quantity))\
.group_by(Alltitles.modelnumber_id, Alltitles.partname_id)\
.order_by(Modelnumbers.modelnumber, Partnames.partname)
示例输出行:
qs: (<Alltitles 486364>, 'H737Q2', 'Resistor', 16) # correct quantity sold
我尝试过将两者结合起来:
qaqs = Alltitles.query.join(Partnames, Partnames.id ==Alltitles.partname_id) \
.join(Modelnumbers, Modelnumbers.id == Alltitles.modelnumber_id) \
.join(Allsoldrecords, Allsoldrecords.title_id == Alltitles.id)\
.add_columns(Modelnumbers.modelnumber,
Partnames.partname,
func.sum(Alltitles.quantityavailable),
func.sum(Alltitles.quantitysold),
(Alltitles.quantityavailable - Alltitles.quantitysold),
func.sum(Allsoldrecords.quantity))\
.group_by(Alltitles.modelnumber_id, Alltitles.partname_id)\
.order_by(Modelnumbers.modelnumber, Partnames.partname)
示例输出行:
both (<Alltitles 486364>, 'H737Q2', 'Resistor', 85, 72, 13, 16)
使用:
func.sum(Alltitles.quantityavailable),
func.sum(Alltitles.quantitysold),
示例输出行:
both: (<Alltitles 486364>, 'H737Q2', 'Resistor', 11, 8, 3, 16)
使用:
Alltitles.quantityavailable,
Alltitles.quantitysold,
将这两个查询组合到“qaqs”会导致Alltitles.quantityavailable和Alltitles.quantitysell的结果不正确,但在查询“qa”和“qs”中是正确的。这里最好的解决办法是什么
目前没有回答
相关问题 更多 >
编程相关推荐