pythonsqlalchemy统计查询中的重复结果

Question

我有个疑问

allcategory =  db.session.query(func.count(marketItem.subcategory).label("help"),Subcategories.subname1)
allcategory = allcategory.join(Subcategories)
allcategory = allcategory.filter(marketItem.subcategory == Subcategories.id)
allcategory = allcategory.filter(marketItem.vendor_id==vendorprofile.id)
allcategory = allcategory.group_by(marketItem.id)
for allcat in allcategory.all():
    print(allcat)

结果输出为

^{pr2}$

我希望他们是

(2, 'Consoles')
(3, 'Paintings')

如果也可以用常规的sql术语来解释，那也会有帮助。在

Answer 1

您应该按Subcategory.subname1分组：

allcategory =  db.session.query(func.count(marketItem.subcategory).label("help"),
                                Subcategories.subname1)
allcategory = allcategory.join(Subcategories)
allcategory = allcategory.filter(marketItem.subcategory==Subcategories.id)
allcategory = allcategory.filter(marketItem.vendor_id==vendorprofile.id)
allcategory = allcategory.group_by(Sucategories.subname1) # < change here
for allcat in allcategory.all():
    print(allcat)

在SQL中，必须按本例中未聚合的字段进行分组Subcategories.subname1。在