是否有更好的方法来执行这样的查询:

SELECT COUNT(*) 
FROM (SELECT DISTINCT DocumentId, DocumentSessionId
      FROM DocumentOutputItems) AS internalQuery

我需要数一下这个表中不同项的数量,但不同项超过两列。

我的查询工作得很好,但我想知道我是否可以只使用一个查询(不使用子查询)得到最终结果


当前回答

如果您使用的是固定长度的数据类型,则可以将其转换为二进制,从而非常容易和快速地完成此操作。假设documententid和DocumentSessionId都是int,因此都是4字节长…

SELECT COUNT(DISTINCT CAST(DocumentId as binary(4)) + CAST(DocumentSessionId as binary(4)))
FROM DocumentOutputItems

My specific problem required me to divide a SUM by the COUNT of the distinct combination of various foreign keys and a date field, grouping by another foreign key and occasionally filtering by certain values or keys. The table is very large, and using a sub-query dramatically increased the query time. And due to the complexity, statistics simply wasn't a viable option. The CHECKSUM solution was also far too slow in its conversion, particularly as a result of the various data types, and I couldn't risk its unreliability.

然而,使用上述解决方案几乎没有增加查询时间(与简单使用SUM相比),并且应该是完全可靠的!它应该能够帮助其他处于类似情况的人,所以我把它贴在这里。

其他回答

如果您使用的是固定长度的数据类型,则可以将其转换为二进制,从而非常容易和快速地完成此操作。假设documententid和DocumentSessionId都是int,因此都是4字节长…

SELECT COUNT(DISTINCT CAST(DocumentId as binary(4)) + CAST(DocumentSessionId as binary(4)))
FROM DocumentOutputItems

My specific problem required me to divide a SUM by the COUNT of the distinct combination of various foreign keys and a date field, grouping by another foreign key and occasionally filtering by certain values or keys. The table is very large, and using a sub-query dramatically increased the query time. And due to the complexity, statistics simply wasn't a viable option. The CHECKSUM solution was also far too slow in its conversion, particularly as a result of the various data types, and I couldn't risk its unreliability.

然而,使用上述解决方案几乎没有增加查询时间(与简单使用SUM相比),并且应该是完全可靠的!它应该能够帮助其他处于类似情况的人,所以我把它贴在这里。

我希望MS SQL也能做一些类似COUNT(DISTINCT A, B)的事情,但它不能。

起初,JayTee的答案对我来说似乎是一个解决方案,但经过一些测试,CHECKSUM()未能创建唯一的值。一个简单的例子是,CHECKSUM(31,467,519)和CHECKSUM(69,1120,823)给出的答案都是55。

然后我做了一些研究,发现微软不建议使用CHECKSUM进行更改检测。在一些论坛上,有人建议使用

SELECT COUNT(DISTINCT CHECKSUM(value1, value2, ..., valueN) + CHECKSUM(valueN, value(N-1), ..., value1))

但这也不令人欣慰。

您可以使用HASHBYTES()函数建议在TSQL校验和难题。然而,这也有一个小的机会不返回唯一的结果。

我建议使用

SELECT COUNT(DISTINCT CAST(DocumentId AS VARCHAR)+'-'+CAST(DocumentSessionId AS VARCHAR)) FROM DocumentOutputItems

我用过这种方法,对我很有效。

SELECT COUNT(DISTINCT DocumentID || DocumentSessionId) 
FROM  DocumentOutputItems

对于我的案例,它提供了正确的结果。

下面是不带subselect的简短版本:

SELECT COUNT(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems

它在MySQL中工作得很好,我认为优化器更容易理解这一点。

编辑:显然我误解了MSSQL和MySQL -对不起,但也许它有帮助。

这段代码使用distinct on 2参数,并提供特定于这些不同值的行数计数。它在MySQL中为我工作,就像一个魅力。

select DISTINCT DocumentId as i,  DocumentSessionId as s , count(*) 
from DocumentOutputItems   
group by i ,s;