小程序 账本小记 统计月收入 月支出 源码分享

统计上的提示:能用sql语句算出来的答案，千万别用遍历去统计

尤其数据量大的情况下，遍历绝对效率低

比如 肯定要统计月收入 月支出 日收入 日支出

可以写个公共方法如下，参数“账本id，开始时间，截止时间“，要统计直接调用传参进去即可：

def get_count_sql(ledgerid,firsttime,lasttime):

connect = pymysql.Connect(host=data_conf["host"],port=data_conf["port"],user=data_conf["user"],passwd=data_conf["passwd"],db=data_conf["db"],charset='utf8')

cursor = connect.cursor()

# 查询收入汇总

income_sql ='''

SELECT SUM(bill_amount) FROM `app_bill_info`

WHERE

ledger_id=

'''+str(ledgerid)+'''

AND

categroy_type=1

AND

bill_crttime>

"'''+str(firsttime)+'''"

AND

bill_crttime<

"'''+str(lasttime)+'''"

AND

bill_isdel=0

'''



#查询支出汇总

expend_sql ='''

SELECT SUM(bill_amount) FROM `app_bill_info`

WHERE

ledger_id=

'''+str(ledgerid)+'''

AND

categroy_type=2

AND

bill_crttime>

"'''+str(firsttime)+'''"

AND

bill_crttime<

"'''+str(lasttime)+'''"

AND

bill_isdel=0

'''

cursor.execute(income_sql)

income_sum=cursor.fetchall()



cursor.execute(expend_sql)

expend_sum=cursor.fetchall()



connect.close()



return income_sum[0][0],expend_sum[0][0]

————————————————