如何无错误合并6条SQL统计查询？解决member_casual列歧义问题

你不需要用JOIN来实现这个需求——JOIN更适合关联有对应关系的表数据，而你的场景是汇总多个独立表中不同用户类型的计数，用UNION ALL结合分组统计或者直接合并各表统计结果更合适，还能彻底避免列名歧义问题。

方法一：合并所有表数据后分组统计 #

先把三个表的数据统一整合，带上表的月份标识，再按月份和用户类型分组计数，一次性得到所有统计结果：

WITH combined_data AS (
  SELECT '01-23' AS month, member_casual
  FROM `test-project-355707.cyclistic_trips.01-23`
  UNION ALL
  SELECT '02-23' AS month, member_casual
  FROM `test-project-355707.cyclistic_trips.02-23`
  UNION ALL
  SELECT '03-23' AS month, member_casual
  FROM `test-project-355707.cyclistic_trips.03-23`
)
SELECT 
  month,
  member_casual,
  COUNT(*) AS count
FROM combined_data
WHERE member_casual IN ('casual', 'member')
GROUP BY month, member_casual
ORDER BY month, member_casual;

方法二：各表单独统计后合并结果 #

如果三个表数据量较大，这种方法效率更高——每个表仅做一次过滤计数，再合并最终结果：

SELECT '01-23' AS month, 'casual' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.01-23`
WHERE member_casual = 'casual'
UNION ALL
SELECT '01-23' AS month, 'member' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.01-23`
WHERE member_casual = 'member'
UNION ALL
SELECT '02-23' AS month, 'casual' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.02-23`
WHERE member_casual = 'casual'
UNION ALL
SELECT '02-23' AS month, 'member' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.02-23`
WHERE member_casual = 'member'
UNION ALL
SELECT '03-23' AS month, 'casual' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.03-23`
WHERE member_casual = 'casual'
UNION ALL
SELECT '03-23' AS month, 'member' AS member_casual, COUNT(*) AS count
FROM `test-project-355707.cyclistic_trips.03-23`
WHERE member_casual = 'member';

列名歧义问题说明 #

你之前遇到的"ambiguous name column"错误，是因为JOIN多表时member_casual列存在于多个表中，数据库无法确定你要引用哪个表的该列。但你的场景根本不需要使用JOIN，上面两种方法都能规避这个问题，同时满足一次性获取所有统计结果的需求。

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