SQL 计数（*）和不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1826120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL count(*) and distinct
提问by Nitish Upreti
Why can't we use count(distinct *)in SQL? As in to count all distinct rows?
为什么我们不能count(distinct *)在SQL中使用？至于计算所有不同的行？
回答by Christian Hayter
select count(*) from (select distinct * from MyTable) as T
Although I strongly suggest that you re-think any queries that use DISTINCT. In a large percentage of cases, GROUP BYis more appropriate (and faster).
尽管我强烈建议您重新考虑任何使用DISTINCT. 在很大比例的情况下，GROUP BY更合适（更快）。
EDIT:Having read the question comments, I should point out that you should neverask the DBMS to do more work than actually needs doing to get a result. If you know in advance that there will not be any duplicated rows in a table, then don't use DISTINCT.
编辑：阅读问题评论后，我应该指出，您永远不应该要求 DBMS 做比实际需要做的更多的工作来获得结果。如果您事先知道表中不会有任何重复的行，则不要使用DISTINCT.
回答by Jason Punyon
You can select all the columns in your table and group by...
您可以选择表格中的所有列并按...分组
SELECT column1, column2, column3, count(*)
FROM someTable
GROUP BY column1, column2, column3
回答by silent
why not?
为什么不？
select
count(distinct name)
from
people
回答by dxh
You can indeed.
你确实可以。
If you've got an identifier, though, you won't have any entirely distinct rows. But you could do for instance:
但是，如果您有标识符，则不会有任何完全不同的行。但你可以这样做：
SELECT COUNT(DISTINCT SenderID) FROM Messages
回答by Adriaan Stander
You can try a CTE in Sql Server 2005
您可以在 Sql Server 2005 中尝试 CTE
;WITH cte AS (
SELECT DISTINCT Val1,Val2, Val3
FROM @Table
)
SELECT COUNT(1)
FROM cte
To answer the question, From the documentation
要回答这个问题，从文档
Specifies that all rows should be counted to return the total number of rows in a table. COUNT() takes no parameters and cannot be used with DISTINCT. COUNT() does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.
指定应计算所有行以返回表中的总行数。COUNT( ) 不带参数，不能与 DISTINCT 一起使用。COUNT() 不需要表达式参数，因为根据定义，它不使用有关任何特定列的信息。COUNT(*) 返回指定表中的行数而不去除重复项。它分别计算每一行。这包括包含空值的行。
回答by Will
COUNT(*) is the number of rows matching a query.
COUNT(*) 是匹配查询的行数。
A row contains unique information such as rowid. All rows are by definition distinct.
一行包含唯一信息，例如 rowid。根据定义，所有行都是不同的。
You must count the distinct instances of values in some field instead.
您必须改为计算某些字段中值的不同实例。
回答by Jose Antonio Padros
some languajes may not be able to handle 'distinct *' so, if you want the distinction made through many columns you might want to use 'distinct ColumnA || ColumnB' , combining the values before judging if they are different. Be mindful whether your variables are numeric and your database handler can make automatic typecast to character strings.
某些语言可能无法处理“distinct *”，因此，如果您希望通过许多列进行区分，则可能需要使用“distinct ColumnA ||” ColumnB' ，在判断它们是否不同之前组合这些值。请注意您的变量是否为数字，并且您的数据库处理程序可以自动对字符串进行类型转换。
回答by May
select count (Tag_no) from tab_raw_tag_value where tag_no in (select distinct tag_no from tab_raw_tag_value)
回答by richo
UberKludge, and may be postgre specific, but
UberKludge，并且可能是特定于 postgre 的，但是
select count( distinct table::text ) from table
