求可单次查询数据库两个不同表的Django查询语句示例

原有实现（两次独立查询） #

data = Insuree.objects.filter(gender='F').count()
data2 = Service.objects.filter(code='F4').count()

方案1：使用Django ORM子查询实现单次查询 #

通过Subquery和aggregate将两个统计逻辑合并为一次数据库查询：

from django.db.models import Subquery, Count, IntegerField

# 定义两个子查询分别计算对应统计值
female_insuree_subquery = Insuree.objects.filter(gender='F').annotate(count=Count('pk')).values('count')
f4_service_subquery = Service.objects.filter(code='F4').annotate(count=Count('pk')).values('count')

# 聚合两个子查询结果，生成单次查询
result = Insuree.objects.aggregate(
    female_insuree_count=Subquery(female_insuree_subquery, output_field=IntegerField()),
    f4_service_count=Subquery(f4_service_subquery, output_field=IntegerField())
)

# 结果格式：{'female_insuree_count': 123, 'f4_service_count': 456}

方案2：使用原生SQL实现单次查询 #

如果对性能要求更高，或者需要更灵活的SQL控制，可以直接执行原生SQL：

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT 
            (SELECT COUNT(*) FROM insuree WHERE gender = 'F') AS female_insuree_count,
            (SELECT COUNT(*) FROM service WHERE code = 'F4') AS f4_service_count
    """)
    row = cursor.fetchone()
    result = {
        'female_insuree_count': row[0],
        'f4_service_count': row[1]
    }

方案对比 #

• ORM子查询：保持Django代码风格，无需手动编写SQL，维护性强，适合常规场景。
• 原生SQL：查询性能略优，适合复杂查询场景或需要精细控制SQL执行逻辑的情况。

内容的提问来源于stack exchange，提问作者MBOU LONTSI