SELECT DISTINCT sd.DEVICE_NAME AS deviceName, r.STATISTICS_DATE AS xValue, r.COLLECTION_VALUE AS yValue FROM ibms_sys_device_collection_report_day r INNER JOIN ibms_sys_device sd ON r.DEVICE_ID = sd.DEVICE_ID INNER JOIN ibms_sys_device_collection dc ON r.COLLECTION_ID = dc.ID INNER JOIN ibms_sys_model_attributes ma ON dc.MODEL_ATT_ID = ma.MODEL_ATT_ID WHERE ma.DEVICE_TYPE_ATTRIBUTE_ID = 188 AND DATE_FORMAT( r.STATISTICS_DATE, '%Y-%m' ) BETWEEN '2023-05-10' and '2023-06-08' AND r.STATISTICS_DATE BETWEEN '2023-05-10' and '2023-06-08' AND FIND_IN_SET( r.DEVICE_ID,',4666,64111,68202') > 0 GROUP BY r.DEVICE_ID, r.STATISTICS_DATE 加快查询速度调优

可以考虑以下几点来加快查询速度：

1. 在 ibms_sys_device_collection_report_day 表中，可以创建统计日期（STATISTICS_DATE）和设备ID（DEVICE_ID）的联合索引，以便更快地执行日期范围过滤和设备ID筛选。

2. 在 ibms_sys_model_attributes 表中，可以创建设备类型属性ID（DEVICE_TYPE_ATTRIBUTE_ID）索引，以便更快地执行设备类型属性ID筛选。

3. 在 ibms_sys_device 表中，可以创建设备ID（DEVICE_ID）索引，以便更快地执行设备ID筛选。

4. 可以使用EXPLAIN命令来分析查询计划，查看是否有慢查询或全表扫描的情况，如果有，可以尝试调整查询条件或创建索引来优化查询。

5. 可以考虑将查询结果缓存起来，避免重复查询相同的数据。

相关问题

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 = '市级'
);

请注意，具体的优化策略可能需要根据实际情况进行调整和测试，以达到最佳的查询性能。

SELECT DISTINCT A.BEGIN_REPAIR_NO, A.PART_NO, A.CREATE_BY, B.EMPLOYEE_NAME AS CREATE_NAME, A.CREATE_TIME FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO SELECT nvl(sum(A.CURRENT_QUANTITY),0) FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.sys_part B ON A.PART_NO = B.PART_NO 将这俩条sql语句 合成一条

可以使用子查询将这两条 SQL 查询语句合并成一条：

SELECT DISTINCT
A.BEGIN_REPAIR_NO,
A.PART_NO,
A.CREATE_BY,
B.EMPLOYEE_NAME AS CREATE_NAME,
A.CREATE_TIME,
NVL((SELECT SUM(CURRENT_QUANTITY)
FROM TPMDATA.PICKING
LEFT JOIN TPMDATA.sys_part ON TPMDATA.PICKING.PART_NO = TPMDATA.sys_part.PART_NO
WHERE TPMDATA.PICKING.BEGIN_REPAIR_NO = A.BEGIN_REPAIR_NO AND TPMDATA.PICKING.PART_NO = A.PART_NO), 0) AS CURRENT_QUANTITY
FROM
TPMDATA.PICKING A
LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO;

这条语句将原本要查询的 SUM(CURRENT_QUANTITY) 作为子查询嵌入到了主查询中，并且增加了 WHERE 子句，以便查询对应的 BEGIN_REPAIR_NO 和 PART_NO。