从多个表中选择COUNT(DISTINCT [name]) [英] SELECT COUNT(DISTINCT [name]) from several tables

问题描述

我可以从一个表中的列中执行以下不同名称(或非重复名称)的SQL Server选择，如下所示:

I can perform the following SQL Server selection of distinct (or non-repeating names) from a column in one table like so:

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

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

But what if I have more than one table (all these tables contain the name field called [Name]) and I need to know the count of non-repeating names in two or more tables.

如果我运行这样的内容:

If I run something like this:

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

我收到一个错误消息，歧义列名'Name'".

I get an error, "Ambiguous column name 'Name'".

PS.这三个表[MyTable1]，[MyTable2]，[MyTable3]都是先前选择的结果.

PS. All three tables [MyTable1], [MyTable2], [MyTable3] are a product of a previous selection.

推荐答案

澄清后，使用:

  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

如果我理解正确，请使用:

If I understand correctly, use:

  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不会，并且速度更快.

UNION will remove duplicates; UNION ALL will not, and is faster for it.

这篇关于从多个表中选择COUNT(DISTINCT [name])的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！