1. 数据准备

-- 创建 MySQL 表
CREATE TABLE cook.shop (
  sid INT COMMENT '卖家ID',
  platform VARCHAR(20) COMMENT '电商平台',
  shop_name VARCHAR(20) COMMENT '店铺名称'
);

INSERT INTO cook.shop VALUES 
  (1, '京东', '京东1号店'),
  (1, '淘宝', '淘宝1号店'),
  (1, '淘宝', '淘宝2号店'),
  (2, '京东', '京东1号店'),
  (2, '京东', ''),
  (2, '拼多多', '拼多多1号店'),
  (3, '淘宝', '淘宝1号店'),
  (3, '', '京东1号店'),
  (3, '拼多多', '拼多多1号店'),
  (4, '淘宝', '淘宝1号店'),
  (4, '京东', '京东1号店'),
  (4, '京东', '京东2号店'),
  (4, '拼多多', '拼多多1号店'),
  (4, '拼多多', '拼多多2号店'),
  (5, '京东', '京东1号店'),
  (5, '京东', '京东1号店');

2. 需求

统计每个卖家ID在每个电商平台的店铺数量，要求：

• 过滤掉 platform = '' 的脏数据
• 店铺名称为''的不计入店铺数量
• 在某电商平台下没有店铺的，应该输出0
• 店铺名称去重后统计

期望结果：

卖家ID	淘宝店铺数	京东店铺数	拼多多店铺数
1	2	1	0
2	0	1	1
3	1	0	1
4	1	2	2
5	0	1	0

3. SQL实现

-- 方法一
SELECT 
  sid AS '卖家账号',
  COUNT(DISTINCT CASE WHEN platform = '淘宝' THEN shop_name END) AS '淘宝店铺数', 
  COUNT(DISTINCT CASE WHEN platform = '京东' THEN shop_name END) AS '京东店铺数',
  COUNT(DISTINCT CASE WHEN platform = '拼多多' THEN shop_name END) AS '拼多多店铺数'
FROM cook.shop 
WHERE platform != '' AND shop_name != '' 
GROUP BY sid;

-- 方法二
SELECT 
  tmp.sid AS '卖家账号', 
  SUM(tmp.tb_shop_num) AS '淘宝店铺数', 
  SUM(tmp.jd_shop_num) AS '淘宝店铺数', 
  SUM(tmp.pdd_shop_num) AS '淘宝店铺数' 
FROM (
  SELECT 
    sid, 
    IF(platform = '淘宝', COUNT(DISTINCT shop_name), 0) AS tb_shop_num,
    IF(platform = '京东', COUNT(DISTINCT shop_name), 0) AS jd_shop_num,
    IF(platform = '拼多多', COUNT(DISTINCT shop_name), 0) AS pdd_shop_num
  FROM cook.shop 
  WHERE platform != '' AND shop_name != ''
  GROUP BY sid, platform) AS tmp
GROUP BY sid;