SELECT shop, DATE_FORMAT( or_time, '%Y-%m' ) or_time, CASE WHEN lopll LIKE '%中通%' THEN '中通' WHEN lopll LIKE '%邮政%' THEN '邮政' WHEN lopll LIKE '%韵达%' THEN '韵达' WHEN lopll LIKE '%顺丰%' THEN '顺丰' END as lopll , COUNT( DISTINCT l_num ) l_num FROM `stock_out` WHERE shop IN ( SELECT shop FROM all_order_data.`shop_channel` WHERE channel = '独唱团' ) AND YEAR ( or_time ) >= 2021 GROUP BY shop, or_time, lopll, l_num怎么查出sum(DISTINCT l_num)

在MySQL中，不能直接使用SUM(DISTINCT l_num)来求去重后的l_num的总和。但可以通过子查询和SUM函数来实现。具体的SQL语句如下：

SELECT  shop, 
        DATE_FORMAT(or_time, '%Y-%m') or_time, 
        CASE 
            WHEN lopll LIKE '%中通%' THEN '中通' 
            WHEN lopll LIKE '%邮政%' THEN '邮政' 
            WHEN lopll LIKE '%韵达%' THEN '韵达' 
            WHEN lopll LIKE '%顺丰%' THEN '顺丰' 
        END AS lopll, 
        COUNT(DISTINCT l_num) l_num,
        (SELECT SUM(cnt) FROM (SELECT COUNT(DISTINCT l_num) AS cnt FROM `stock_out` WHERE shop = t.shop AND or_time = t.or_time AND lopll = t.lopll GROUP BY l_num) AS temp) AS l_num_sum
FROM `stock_out` t
WHERE shop IN (SELECT shop FROM all_order_data.`shop_channel` WHERE channel = '独唱团')
    AND YEAR(or_time) >= 2021
GROUP BY shop, or_time, lopll, l_num

在这个SQL语句中，我们使用了子查询和临时表来计算去重后的l_num的总和。具体来说，我们在最外层查询中添加了一个子查询：

(SELECT SUM(cnt) FROM (SELECT COUNT(DISTINCT l_num) AS cnt FROM `stock_out` WHERE shop = t.shop AND or_time = t.or_time AND lopll = t.lopll GROUP BY l_num) AS temp) AS l_num_sum

这个子查询首先是在内部进行了一次查询，使用了GROUP BY l_num来求出每个组（每个shop、or_time、lopll的组合）中去重后的l_num的个数。然后再在外部使用SUM函数对这些个数进行求和，得到了去重后的l_num的总和。