如何写sqlalchemy查询语句?

Question

数据库是 oracle 11g。项目是 flask。

有这样两个表：

我想查询出这样一个表格

序号	部门	角色	员工	电话	月份	管户数	本月管户总余额	上月管户总余额	新增余额

其中 管户数 是员工当月所关联的用户的总数，本月管户总余额 是当月所关联的用户的余额总数，上月管户总余额 是上月所关联的用户的余额总额，新增余额 是本月管户总余额 减去 上月管户总余额 。

本人使用下面的 sqlalchemy 写法是可以查询出来的：

sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
                               g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
                               g2.balance.label('last_balance')).filter(
            OusiStaff.phone == g1.staff_phone, g1.staff_phone == g2.staff_phone,
            OusiStaff.phone == current_user.phone, g1.name==g2.name,
            func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month, 'yyyy-mm')
        ).subquery()
database = db.session.query(sbq.c.department, sbq.c.role, sbq.c.staff_name, sbq.c.staff_phone, sbq.c.guest_name,
                                    sbq.c.month, func.sum(sbq.c.balance), func.sum(sbq.c.last_balance)). \
            filter(sbq.c.month == date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,
                                                                           sbq.c.staff_name, sbq.c.staff_phone,
                                                                           sbq.c.guest_name,
                                                                           sbq.c.month)

但是，这里有一个问题，那就是 数据库里的数据是从 “2018-01” 这个月份开始，要是查询 “2018-01” 当月的情况的话，由于没有上月余额的数据，那么 “2018-01” 这个余额就无法显示数据。

我的理想结果是，查询 “2018-01” 当月的数据的时候，能够显示，并且把上月的余额设置为 0 。

这个用 sqlalchemy 该如何写？万分感谢。

Answer 1

努力了一天，终于测试出来了。

sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
                               g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
                               func.nvl(db.session.query(g2.balance).filter(
                                   g1.name == g2.name,
                                   func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,
                                                                                                          'yyyy-mm')
                               ), 0).label('last_balance')).filter(
            OusiStaff.phone == g1.staff_phone,
            OusiStaff.phone == current_user.phone
        ).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
                   g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance).subquery()
        database = db.session.query(sbq.c.department, sbq.c.role, sbq.c.staff_name, sbq.c.staff_phone,
                                    sbq.c.month, func.count(sbq.c.guest_name).label('members'),
                                    func.sum(sbq.c.balance).label('balance'),
                                    func.sum(sbq.c.last_balance).label('last_balance')). \
            filter(sbq.c.month == date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,
                                                                           sbq.c.staff_name, sbq.c.staff_phone,
                                                                           sbq.c.month)

其中的难点一是利用 func.to_date 和 func.add_months 对上个月这个时间进行关联查询（凡是数据库支持的函数，在 sqlalchemy 中都可以使用 func 加函数名来操作。），二是利用 func.nvl 函数来对初始化数据进行赋值 0 的操作。

Answer 2

oracle 不熟悉，与其写个复杂的查询，不如剥离部分统计用python实现。
数据库按员工+月份分组后直接查出员工信息、管户数及当月管户总余额。
然后写个python方法专门统计本月余额、上月余额及差额。