一张快递表:表中字段shop、or_time、lopll、l_num分别为店铺,出库日期,快递公司,快递单号,同一个快递单号为一个包裹,需要查询每个店铺每个月每个物流公司分别发了多少包裹
时间: 2024-02-05 07:13:44 浏览: 120
可以使用如下SQL语句查询每个店铺每个月每个物流公司分别发了多少包裹:
```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)`用于统计每个物流公司每个月发了多少个不同的快递单号,即不同的包裹。
相关问题
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的总和。
阅读全文