从多个表中选择 COUNT(DISTINCT [name])

Question

我可以执行以下 SQL Server 从一个表中的列中选择不同（或不重复的名称）的操作，如下所示：

SELECT COUNT(DISTINCT [Name]) FROM [MyTable]

但是，如果我有多个表（所有这些表都包含名为 [Name] 的名称字段）并且我该怎么办？需要知道两个或多个表中非重复名称的计数。

如果我运行这样的命令：

SELECT COUNT(DISTINCT [Name]) FROM [MyTable1], [MyTable2], [MyTable3]

我收到错误“列名‘名称’不明确”。

附言。所有三个表 [MyTable1]、[MyTable2]、[MyTable3] 都是先前选择的产物。

Answer 1

澄清后，使用：

  SELECT x.name, COUNT(x.[name])
    FROM (SELECT [name]
            FROM [MyTable]
          UNION ALL
          SELECT [name]
            FROM [MyTable2]
          UNION ALL
          SELECT [name]
            FROM [MyTable3]) x
GROUP BY x.name

如果我理解正确，使用：

  SELECT x.name, COUNT(DISTINCT x.[name])
    FROM (SELECT [name]
            FROM [MyTable]
          UNION ALL
          SELECT [name]
            FROM [MyTable2]
          UNION ALL
          SELECT [name]
            FROM [MyTable3]) x
GROUP BY x.name

UNION 将删除重复项； UNION ALL 不会，而且速度更快。

Answer 2

编辑：看到最近的评论后必须更改。

这能给你你想要的吗？合并所有表中的行后，这将给出每个人的计数。

SELECT [NAME], COUNT(*) as TheCount
FROM
    (
     SELECT [Name] FROM [MyTable1]
     UNION ALL
     SELECT [Name] FROM [MyTable2]
     UNION ALL
     SELECT [Name] FROM [MyTable3]
     ) AS [TheNames]
GROUP BY [NAME]

Answer 3

这是另一种方法：

SELECT x.name, SUM(x.cnt)
FROM ( SELECT [name], COUNT(*) AS cnt
       FROM [MyTable]
       GROUP BY [name]
     UNION ALL
       SELECT [name], COUNT(*) AS cnt
       FROM [MyTable2]
       GROUP BY [name]
     UNION ALL
       SELECT [name], COUNT(*) AS cnt
       FROM [MyTable3]
       GROUP BY [name]
     ) AS x
GROUP BY x.name

Answer 4

如果您每个表的列数不同，例如：

• table1 有 3 列，
• table2 有 2 列，
• table3 有 1 列

并且您想要计算不同列名称的不同值的数量，那么什么在 AthenaSQL 中，使用 CROSS JOIN 对我很有用，因为您的输出只有一行，它只是 1 个组合：

SELECT * FROM (
SELECT COUNT(DISTINCT name1) as amt_name1,
       COUNT(DISTINCT name2) as amt_name2,
       COUNT(DISTINCT name3) as amt_name3,
FROM table1 ) t1
CROSS JOIN
(SELECT COUNT(DISTINCT name4) as amt_name4,
        COUNT(DISTINCT name5) as amt_name5,
        MAX(t3.amt_name6) as amt_name6
 FROM table2
 CROSS JOIN
 (SELECT COUNT(DISTINCT name6) as amt_name6
  FROM table3) t3) t2

将返回一个包含一行的表，并且他们的计数：

amt_name1 | amt_name2 | amt_name3 | amt_name4 | amt_name5 | amt_name6
    4123  |    675    |    564    |    2346   |   18667   |    74567

Answer 5

一个更新的解决方案，来自 PostgreSQL 示例，获得所有唯一股票（在本例中）名称的授权总数，包含在一个漂亮的视图中：

CREATE OR REPLACE VIEW data.view_count_distinct_symbols 
  AS
    SELECT COUNT(*) 
      FROM (SELECT DISTINCT symbol_name FROM data.d_bats_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_bats_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nasdaq_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nasdaq_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_arca_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_arca_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_mkt_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_mkt_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_stock);

...

    |count 
    |bigint
-----------
    | 28794