SQL统计每行不同子行数量及影视库多身份人员筛选技术求助

问题1：如何统计每行中不同子行的数量？ #

这个需求分两种常见场景，我分别给你举例说明：

场景1：统计单表每行内多列的不同值数量 #

比如你有一行包含col1、col2、col3三个字段，想统计这三个字段里不重复值的个数。

通用方案（适配多数数据库）

把列转成行，再分组去重统计：

SELECT 
  t.id,
  COUNT(DISTINCT val) AS distinct_value_count
FROM your_table t
CROSS JOIN (
  SELECT t.col1 AS val UNION ALL
  SELECT t.col2 AS val UNION ALL
  SELECT t.col3 AS val
) AS column_values
WHERE val IS NOT NULL -- 可选：排除NULL值，根据需求调整
GROUP BY t.id;

数据库专属简化方案

• PostgreSQL：利用数组去重特性

  SELECT 
    id,
    array_length(ARRAY(SELECT DISTINCT unnest(array[col1, col2, col3])), 1) AS distinct_value_count
  FROM your_table;
  

• MySQL：用CASE判断（适合列数少的情况）

  SELECT 
    id,
    CASE 
      WHEN col1 = col2 AND col2 = col3 THEN 1
      WHEN col1 = col2 OR col1 = col3 OR col2 = col3 THEN 2
      ELSE 3 
    END AS distinct_value_count
  FROM your_table;
  

场景2：统计主表行对应的关联子表中不同值的数量 #

比如主表是Orders，子表Order_Items记录订单商品，想统计每个订单里不同商品类型的数量：

SELECT 
  o.order_id,
  COUNT(DISTINCT oi.product_type) AS distinct_product_types
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

问题2：筛选同时身为演员、导演且参与制片的人员中，涉及电影国家数量最多的人 #

首先梳理你的数据库关联逻辑：Person是所有人员的基础表，Actor、Director分别标记人员的演员、导演身份，Actor_Movie关联演员和电影，Movie里的id_director关联导演。这里我假设存在Producer表（标记制片身份）和Producer_Movie表（关联制片人和电影）——如果你的Schema里制片是其他存储方式，比如Movie有id_producer字段，我会在代码里标注调整点。

完整SQL解决方案 #

-- 第一步：筛选同时拥有演员、导演、制片三种身份的人员
WITH multi_role_people AS (
  SELECT p.id_person, p.name
  FROM Person p
  JOIN Actor a ON p.id_person = a.id_actor
  JOIN Director d ON p.id_person = d.id_director
  JOIN Producer pr ON p.id_person = pr.id_producer -- 若制片是Movie的id_producer字段，可删除这行
),
-- 第二步：收集这些人员参与的所有电影（去重同一电影）
person_movies AS (
  -- 作为演员参与的电影
  SELECT mrp.id_person, m.country
  FROM multi_role_people mrp
  JOIN Actor_Movie am ON mrp.id_person = am.id_actor
  JOIN Movie m ON am.id_movie = m.id_movie
  
  UNION -- 用UNION自动去重同一电影（避免重复统计国家）
  
  -- 作为导演参与的电影
  SELECT mrp.id_person, m.country
  FROM multi_role_people mrp
  JOIN Movie m ON mrp.id_person = m.id_director
  
  UNION
  
  -- 作为制片参与的电影：若制片是Movie的id_producer字段，替换为JOIN Movie m ON mrp.id_person = m.id_producer
  SELECT mrp.id_person, m.country
  FROM multi_role_people mrp
  JOIN Producer_Movie pm ON mrp.id_person = pm.id_producer
  JOIN Movie m ON pm.id_movie = m.id_movie
),
-- 第三步：统计每个人员涉及的不同国家数量
person_country_stats AS (
  SELECT 
    mrp.id_person,
    mrp.name,
    COUNT(DISTINCT pm.country) AS unique_country_count
  FROM person_movies pm
  JOIN multi_role_people mrp ON pm.id_person = mrp.id_person
  WHERE pm.country IS NOT NULL -- 可选：排除无国家信息的电影
  GROUP BY mrp.id_person, mrp.name
),
-- 第四步：找到最大的国家数量
max_country_count AS (
  SELECT MAX(unique_country_count) AS max_count
  FROM person_country_stats
)
-- 最终：筛选出国家数量等于最大值的人员
SELECT id_person, name, unique_country_count
FROM person_country_stats
JOIN max_country_count mcc ON person_country_stats.unique_country_count = mcc.max_count;

关键注意点 #

1. 关联关系验证：确保Actor.id_actor、Director.id_director、Producer.id_producer都正确关联到Person.id_person，这是整个查询的基础。
2. 去重处理：用UNION而不是UNION ALL，避免同一电影被多次统计（比如某人既是某电影的演员又是导演）。
3. NULL处理：如果Movie.country存在NULL值，记得用WHERE country IS NOT NULL排除，避免无效统计。
4. 多结果兼容：如果有多个人员的国家数量并列第一，这个查询会返回所有符合条件的人，符合需求。

内容的提问来源于stack exchange，提问作者CONGOBILL