鍍金池/ 問答/Python  數(shù)據(jù)庫/ 如何寫sqlalchemy查詢語句?

如何寫sqlalchemy查詢語句?

數(shù)據(jù)庫是 oracle 11g。項目是 flask。

有這樣兩個表：

clipboard.png

clipboard.png

我想查詢出這樣一個表格

序號 部門 角色 員工 電話 月份 管戶數(shù) 本月管戶總余額 上月管戶總余額 新增余額

其中 管戶數(shù) 是員工當月所關聯(lián)的用戶的總數(shù)，本月管戶總余額 是當月所關聯(lián)的用戶的余額總數(shù)，上月管戶總余額 是上月所關聯(lián)的用戶的余額總額，新增余額 是本月管戶總余額 減去 上月管戶總余額 。

本人使用下面的 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)

但是，這里有一個問題，那就是 數(shù)據(jù)庫里的數(shù)據(jù)是從 “2018-01” 這個月份開始，要是查詢 “2018-01” 當月的情況的話，由于沒有上月余額的數(shù)據(jù)，那么 “2018-01” 這個余額就無法顯示數(shù)據(jù)。

我的理想結(jié)果是，查詢 “2018-01” 當月的數(shù)據(jù)的時候，能夠顯示，并且把上月的余額設置為 0 。

這個用 sqlalchemy 該如何寫？萬分感謝。

回答
編輯回答
歆久

oracle 不熟悉，與其寫個復雜的查詢，不如剝離部分統(tǒng)計用python實現(xiàn)。
數(shù)據(jù)庫按員工+月份分組后直接查出員工信息、管戶數(shù)及當月管戶總余額。
然后寫個python方法專門統(tǒng)計本月余額、上月余額及差額。

2018年3月1日 12:39
編輯回答
笨小蛋

努力了一天，終于測試出來了。

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 對上個月這個時間進行關聯(lián)查詢（凡是數(shù)據(jù)庫支持的函數(shù)，在 sqlalchemy 中都可以使用 func 加函數(shù)名來操作。），二是利用 func.nvl 函數(shù)來對初始化數(shù)據(jù)進行賦值 0 的操作。

2018年3月12日 01:35