如何写sqlalchemy查询语句?

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

有这样两个表:

clipboard.png

clipboard.png

我想查询出这样一个表格

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

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

本人使用下面的 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 该如何写?万分感谢。

阅读 4.1k
2 个回答

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

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_datefunc.add_months 对上个月这个时间进行关联查询(凡是数据库支持的函数,在 sqlalchemy 中都可以使用 func 加函数名来操作。),二是利用 func.nvl 函数来对初始化数据进行赋值 0 的操作。

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

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题