sqlalchemy 简单的case_when统计

需求; 统计不同组织下的成绩风险等级类型个数;

attacker_group = (
    db.session.query(ReportResult, Report, Organization)
    .join(Report, Report.id == ReportResult.report_id)
    .join(Organization, Report.organization_id == Organization.id)
    .filter(*filters)
    .group_by(ReportResult.dimension,Organization.title)
    .with_entities(
        ReportResult.dimension,
        Organization.title,
        func.sum(case([(level == 4, 1)], else_=0)).label(
            "D"
        ),
        func.sum(case([(level == 3, 1)], else_=0)).label(
            "C"
        ),
        func.sum(case([(level == 2, 1)], else_=0)).label(
            "B"
        ),
        func.sum(case([(level == 1, 1)], else_=0)).label(
            "A"
        ),
        func.sum(level).label(
            "total"
        )
    )
    .order_by(desc("total")).all()
)