关于sql优化得查询语句

问题遇到的现象和发生背景

请问这个sql怎么优化

问题相关代码，请勿粘贴截图

SELECT
COUNT( DISTINCT a.deviceNo ) AS deviceSum,
(
SELECT
COUNT( DISTINCT b.deviceNo )
FROM
dsbs_device_list b
WHERE
b.online = 1

    AND b.city = a.city 
    AND b.district = a.district 
GROUP BY
    b.city,
    b.district 
) AS isolateSum,
CONCAT((
        ROUND(((
                SELECT
                    COUNT( DISTINCT b.deviceNo ) 
                FROM
                    dsbs_device_list b WHERE b.`online` = 1 

                    AND b.city = a.city 
                    AND b.district = a.district 
                GROUP BY
                    b.city,
                    b.district 
                    )* 100 /(
                    COUNT( DISTINCT a.deviceNo ))),
            2 
        )),
    '%' 
) AS onlineRate,
a.city,
a.district 

FROM
dsbs_device_list a
WHERE
city IS NOT NULL

GROUP BY
a.city,
a.district

运行结果及报错内容

时间太长

尽量不要用子查询，可以换成使用表连接。

用count having筛选，不用在子查询里面重新select。

1. 思路: 首先根据, city, district 分组查询设备总数和在线数, 查询完了 在业务代码里面 遍历数据计算比例
2. 我看你的 online 字段 1 表示在线, 推测 0 是不在线, 写出sql如下:

SELECT
    COUNT( DISTINCT ddl.deviceNo ) AS deviceSum,
    SUM( ddl.ONLINE ) AS isolateSum,
    
FROM
    dsbs_device_list ddl 
WHERE
    ddl.city IS NOT NULL 
GROUP BY
    ddl.city,
    ddl.district

    SELECT
    COUNT( DISTINCT a.deviceNo ) AS deviceSum,
    COUNT(DISTINCT case when a.online = 1 then  a.deviceNo else null end) as isolateSum,
    CONCAT(ROUND(COUNT(DISTINCT case when a.online = 1 then  a.deviceNo else null end)/COUNT( DISTINCT a.deviceNo)*100,2),'%') as onlineRate
    FROM
    dsbs_device_list a
    WHERE
    city IS NOT NULL

    GROUP BY
    a.city,
    a.district

SELECT
COUNT( DISTINCT a.deviceNo ) AS deviceSum,
COUNT( DISTINCT b.deviceNo ) AS isolateSum,
CONCAT((ROUND((COUNT( DISTINCT b.deviceNo ))* 100 /(COUNT( DISTINCT a.deviceNo )),2 )),'%' ) AS onlineRate,
a.city,
a.district 
FROM  dsbs_device_list a
left join dsbs_device_list B 
ON b.city = a.city 
AND b.district = a.district
AND b.online = 1 
WHERE
A.city IS NOT NULL
AND B.city IS NULL 
AND B.district  IS NULL 
GROUP BY
a.city,
a.district