请优化以下代码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 14:42:16 浏览: 15
可以将代码中的多次 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 查询的次数,提高代码的效率。
相关问题
SELECT DISTINCT ( A.DATA_TYPE ) AS DATA_TYPE, A.DATA_VALUE AS TSL, IFNULL(( SELECT B.DATA_VALUE FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA B WHERE B.IS_DELETED = 0 AND B.DATA_DIMENSION = '期末' AND B.LEVEL = 2 AND B.DATA_NAME = '举报' AND B.DATA_AREA = '市级' AND B.DATA_TYPE = A.DATA_TYPE AND B.DATA_TIME = ( SELECT MAX( DATA_TIME ) FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA WHERE IS_DELETED = 0 AND DATA_DIMENSION = '期末' AND DATA_NAME = '举报' AND LEVEL = 2 AND DATA_AREA = '市级' ) ), 0 ) AS JBL, CURRENT_ID FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA A WHERE A.IS_DELETED = 0 AND A.DATA_DIMENSION = '期末' AND A.LEVEL = 2 AND A.DATA_NAME = '投诉' AND A.DATA_AREA = '市级' AND A.DATA_TIME = ( SELECT MAX( DATA_TIME ) FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA WHERE IS_DELETED = 0 AND DATA_DIMENSION = '期末' AND DATA_NAME = '投诉' AND LEVEL = 2 AND DATA_AREA = '市级')这个sql 优化
对于这个SQL查询语句的优化,可以考虑以下几点:
1. 使用JOIN语句代替子查询:将子查询转换为JOIN语句,可以提高查询性能。
2. 添加适当的索引:根据查询条件和连接条件,为相关表添加合适的索引,以加快查询速度。
3. 减少重复的条件判断:在子查询和主查询中,有一些条件判断是重复的,可以将它们提取出来,避免重复计算。
4. 使用表别名:为表和字段使用简短的别名,提高查询语句的可读性。
下面是对原始SQL查询语句进行优化的示例:
SELECT DISTINCT
A.DATA_TYPE AS DATA_TYPE,
A.DATA_VALUE AS TSL,
IFNULL(B.DATA_VALUE, 0) AS JBL,
A.CURRENT_ID
FROM
YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA A
LEFT JOIN
(
SELECT
DATA_TYPE, DATA_VALUE
FROM
YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA
WHERE
IS_DELETED = 0
AND DATA_DIMENSION = '期末'
AND DATA_NAME = '举报'
AND LEVEL = 2
AND DATA_AREA = '市级'
AND DATA_TIME = (
SELECT MAX(DATA_TIME)
FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA
WHERE
IS_DELETED = 0
AND DATA_DIMENSION = '期末'
AND DATA_NAME = '举报'
AND LEVEL = 2
AND DATA_AREA = '市级'
)
) B ON A.DATA_TYPE = B.DATA_TYPE
WHERE
A.IS_DELETED = 0
AND A.DATA_DIMENSION = '期末'
AND A.LEVEL = 2
AND A.DATA_NAME = '投诉'
AND A.DATA_AREA = '市级'
AND A.DATA_TIME = (
SELECT MAX(DATA_TIME)
FROM YXDDZH_MIDDLE.GZ_SCREEN_JGSY_SDHJ_SPFW_CITY_AREA_DATA
WHERE
IS_DELETED = 0
AND DATA_DIMENSION = '期末'
AND DATA_NAME = '投诉'
AND LEVEL = 2
AND DATA_AREA = '市级'
);
请注意,具体的优化策略可能需要根据实际情况进行调整和测试,以达到最佳的查询性能。
sql优化 SELECT PARENT_ORG_ID FROM TD_BFM_ORG WHERE PARENT_ORG_ID NOT IN (SELECT DISTINCT ORG_ID FROM TD_BFM_ORG)
这个 SQL 语句的意思是查询 `TD_BFM_ORG` 表中所有不在 `TD_BFM_ORG` 表中 `ORG_ID` 字段中出现过的 `PARENT_ORG_ID` 字段值。
优化可以从以下几个方面入手:
1. 使用 `NOT EXISTS` 替代 `NOT IN`,因为 `NOT IN` 的子查询结果集中如果存在 NULL 值,那么整个查询结果都是空的,而 `NOT EXISTS` 没有这个问题。
2. 添加索引:可以为 `TD_BFM_ORG` 表的 `PARENT_ORG_ID` 字段和 `ORG_ID` 字段分别创建索引,这样可以加快查询速度。
3. 减少子查询:可以将 `DISTINCT` 替换成 `GROUP BY`,这样可以减少子查询的数量。
优化后的 SQL 语句如下:
```sql
SELECT PARENT_ORG_ID
FROM TD_BFM_ORG t1
WHERE NOT EXISTS (
SELECT 1
FROM TD_BFM_ORG t2
WHERE t2.ORG_ID = t1.PARENT_ORG_ID
)
```
并且为 `PARENT_ORG_ID` 和 `ORG_ID` 分别创建索引:
```sql
CREATE INDEX idx_parent_org_id ON TD_BFM_ORG(PARENT_ORG_ID);
CREATE INDEX idx_org_id ON TD_BFM_ORG(ORG_ID);
```