SELECT shop,DATE_FORMAT(or_time,'%Y-%m') or_time,lopll,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,DATE_FORMAT(or_time,'%Y-%m'),lopll,l_num查询出每个shop，每个月,每个lopll的次数（count(l_num)）

你可以尝试以下SQL语句：

SELECT shop,DATE_FORMAT(or_time,'%Y-%m') or_time,lopll,l_num,COUNT(l_num) num_of_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,DATE_FORMAT(or_time,'%Y-%m'),lopll,l_num;

这个查询语句会先从 `shop_channel` 表中选取所有 `channel` 为 "独唱团" 的店铺，并且选择 `or_time` 大于等于 2021 年的记录。然后，按照 `shop`、`or_time`、`lopll`、`l_num` 进行分组，并且计算每个组内 `l_num` 出现的次数。最后，将这些信息返回。

希望对你有帮助。

相关问题

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的总和。

一张快递表：表中字段shop、or_time、lopll、l_num分别为店铺，出库日期，快递公司，快递单号，同一个快递单号为一个包裹，需要查询每个店铺每个月每个物流公司分别发了多少包裹

可以使用如下SQL语句查询每个店铺每个月每个物流公司分别发了多少包裹：

SELECT 
    shop,
    DATE_FORMAT(or_time, '%Y-%m') AS month,
    lopl1 AS logistics_company,
    COUNT(DISTINCT l_num) AS package_count
FROM 
    express_table
GROUP BY 
    shop,
    month,
    logistics_company;

其中，`DATE_FORMAT(or_time, '%Y-%m')`将出库日期格式化为年月，方便按照月份进行分组；`COUNT(DISTINCT l_num)`用于统计每个物流公司每个月发了多少个不同的快递单号，即不同的包裹。