请优化以下代码dd = [] sql = '''select distinct data_z from luzhi_jilu_all where id_ts='{}' '''.format(id) co.to_sql(sql) jg = cs.fetchall() for j in jg: sql = '''select count(*) from luzhi_jilu_all where id_ts='{0}' and date_z='{1}' and isDelete='完成' '''.format(id,j[0]) co.to_sql(sql) n1 = cs.fetchone() sql = '''select count(*) from luzhi_jilu_all where id_ts='{0}' and date_z='{1}' and isDelete!='完成' '''.format(id,j[0]) co.to_sql(sql) n2 = cs.fetchone() dd.append([j[0],n1[0],n2[0]])
时间: 2024-03-16 10:42:16 浏览: 110
大家来进行SQL优化
可以将代码中的多次 SQL 查询优化为一次查询,将查询结果存储到字典中,再遍历列表进行统计。以下是优化后的代码:
```
sql = '''select date_z, count(case when isDelete='完成' then 1 else null end) as n1, count(case when isDelete!='完成' then 1 else null end) as n2 from luzhi_jilu_all where id_ts='{}' group by date_z'''.format(id)
co.to_sql(sql)
jg = cs.fetchall()
dd = [[j[0], j[1], j[2]] for j in jg]
```
这样做可以减少 SQL 查询的次数,提高代码的效率。
阅读全文