使用 DISTINCT 关键字获得更高的查询结果?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

假设我有一个包含 100,000 个用户 ID 的表(用户 ID 是一个整数).当我运行像

Say I have a table with 100,000 User IDs (UserID is an int). When I run a query like 

SELECT COUNT(Distinct User ID) from tableUserID

我得到的结果比以下语句的结果要高:

the result I get is HIGHER than the result from the following statement:

SELECT COUNT(User ID) from tableUserID

我认为 Distinct 意味着 unique，这意味着结果较低.什么会导致这种差异，我将如何识别那些没有出现在第二个查询中的用户 ID?

I thought Distinct implied unique, which would mean a lower result. What would cause this discrepancy and how would I identify those user IDs that don't show up in the 2nd query?

谢谢

**

**

大家好

我真诚地道歉，因为我应该不厌其烦地在本地环境中重现此内容.但我只是想看看是否就此达成了普遍共识.以下是完整的详细信息:

I sincerely apologize as I should've taken the trouble to reproduce this in my local environment. But I just wanted to see if there was a general consensus about this. Here are the full details:

该查询是 2 个表之间的内部联接的结果.一个人有这个信息:

The query is a result of an inner join between 2 tables. One has this information:

TABLE ACTIVITY  (NO PRIMARY KEY)
UserID  int   (not Nullable)
JoinDate    datetime
Status  tinyint
LeaveDate   datetime
SentAutoMessage tinyint
SectionDetails  varchar

这是第二个表:

TABLE USER_INFO  (CLUSTERED PRIMARY KEY)
UserID  int    (not Nullable)
UserName    varchar
UserActive  int
CreatedOn   datetime
DisabledOn      datetime

表在 UserID 上连接，在原始 2 个查询中选择的 UserID 是 TABLE ACTIVITY 中的一个.

The tables are joined on UserID and the UserID being selected in the original 2 queries is the one from the TABLE ACTIVITY.

希望这能澄清问题.

推荐答案

这在技术上不是一个答案，但既然我花时间分析了这个，我不妨发布一下(虽然我有被否决的风险).

This is not technically an answer, but since I took time to analyze this, I might as well post it (although I have the risk of being down voted).

我无法重现所描述的行为.

There was no way I could reproduce the described behavior.

这是场景:

declare @table table ([user id] int)

insert into @table values 
(1),(1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(2),(2),(null),(null)

以下是一些查询及其结果:

And here are some queries and their results:

SELECT COUNT(User ID) FROM @table --error: this does not run
SELECT COUNT(dsitinct User ID) FROM @table --error: this does not run
SELECT COUNT([User ID]) FROM @table --result: 13 (nulls not counted)
SELECT COUNT(distinct [User ID]) FROM @table --result: 2 (nulls not counted)

还有一些有趣的事情:

SELECT user --result: 'dbo' in my sandbox DB
SELECT count(user) from @table --result: 15 (nulls are counted because user value
                                             is not null)
SELECT count(distinct user) from @table --result: 1 (user is the same  
                                                     value always)

我发现您能够完全按照您描述的方式运行查询非常奇怪.您必须让我们知道表结构和数据才能获得进一步帮助.

I find it very odd that you are able to run the queries exactly how you described. You'd have to let us know the table structure and the data to get further help.

相关文章