blocks|key|1231332|text|希望这个作品我写在prima+vista上|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231333|SELECT+COUNT(*)+
FROM+DocumentOutputItems+
GROUP+BY+DocumentId,+DocumentSessionId|code-block|syntax|javascript|1231334|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Hope this works i am writing on prima vista

<pre><code>SELECT COUNT(*) 
FROM DocumentOutputItems 
GROUP BY DocumentId, DocumentSessionId
</code></pre>

blocks|key|1231412|text|下面是一个不带subselect的简短版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231413|SELECT+COUNT(DISTINCT+DocumentId,+DocumentSessionId)+FROM+DocumentOutputItems|code-block|syntax|javascript|1231414|它在MySQL中工作得很好，我认为优化器更容易理解它。|1231415|编辑:显然我读错了MSSQL和MySQL+-抱歉，但也许它还是有帮助的。|1231416|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here's a shorter version without the subselect:

<pre><code>SELECT COUNT(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems
</code></pre>

It works fine in MySQL, and I think that the optimizer has an easier time understanding this one.

Edit: Apparently I misread MSSQL and MySQL - sorry about that, but maybe it helps anyway.

blocks|key|1231490|text|如果你只有一个字段可以“区分”，你可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231491|SELECT+COUNT(DISTINCT+DocumentId)+
FROM+DocumentOutputItems|code-block|syntax|javascript|1231492|这确实会返回与原始查询计划相同的查询计划，就像使用SET+SHOWPLAN_ALL+ON进行测试一样。然而，您使用了两个字段，因此您可以尝试一些疯狂的东西，比如：|1231493|++++SELECT+COUNT(DISTINCT+convert(varchar(15),DocumentId)%2B'%7C~%7C'%2Bconvert(varchar(15),+DocumentSessionId))+
++++FROM+DocumentOutputItems|1231494|但是如果涉及到NULLs，你就会遇到问题。我只会坚持原来的查询。|1231495|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

if you had only one field to "DISTINCT", you could use:

<pre><code>SELECT COUNT(DISTINCT DocumentId) 
FROM DocumentOutputItems
</code></pre>

and that does return the same query plan as the original, as tested with SET SHOWPLAN_ALL ON. However you are using two fields so you could try something crazy like:

<pre><code> SELECT COUNT(DISTINCT convert(varchar(15),DocumentId)+'|~|'+convert(varchar(15), DocumentSessionId)) 
 FROM DocumentOutputItems
</code></pre>

but you'll have issues if NULLs are involved. I'd just stick with the original query.

blocks|key|1446389|text|您的查询没有任何问题，但您也可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446390|WITH+internalQuery+(Amount)
AS
(
++++SELECT+(0)
++++++FROM+DocumentOutputItems
++GROUP+BY+DocumentId,+DocumentSessionId
)
SELECT+COUNT(*)+AS+NumberOfDistinctRows
++FROM+internalQuery|code-block|syntax|javascript|1446391|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

There's nothing wrong with your query, but you could also do it this way:

<pre><code>WITH internalQuery (Amount)
AS
(
 SELECT (0)
 FROM DocumentOutputItems
 GROUP BY DocumentId, DocumentSessionId
)
SELECT COUNT(*) AS NumberOfDistinctRows
 FROM internalQuery
</code></pre>

blocks|key|1446436|text|如果您试图提高性能，可以尝试在两列的哈希值或连接值上创建持久化计算列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446437|持久化后，如果列是确定性的，并且您使用的是“合理的”数据库设置，则可以对其进行索引和/或在其上创建统计信息。|1446438|我相信计算列的distinct计数将等同于您的查询。|1446439|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it. 

I believe a distinct count of the computed column would be equivalent to your query.

blocks|key|1446474|text|我希望MS+SQL也能做一些像计数(不同的A，B)这样的事情。但它不能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446475|起初，JayTee的答案对我来说似乎是一个解决方案，但在一些测试中，CHECKSUM()无法创建唯一的值。一个简单的例子是，校验和(31,467,519)和校验和(69,1120,823)给出了相同的答案，即55。|1446476|然后我做了一些研究，发现微软不推荐使用校验和进行更改检测。在一些论坛中，一些人建议使用|1446477|SELECT+COUNT(DISTINCT+CHECKSUM(value1,+value2,+...,+valueN)+%2B+CHECKSUM(valueN,+value(N-1),+...,+value1))|code-block|syntax|javascript|1446478|但这也不是令人舒服的。|1446479|您可以按照TSQL+CHECKSUM+conundrum中的建议使用HASHBYTES()函数。然而，这也有可能不返回唯一的结果。|offset|length|1446480|我建议使用|1446481|SELECT+COUNT(DISTINCT+CAST(DocumentId+AS+VARCHAR)%2B'-'%2BCAST(DocumentSessionId+AS+VARCHAR))+FROM+DocumentOutputItems|1446482|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/12841331/tsql-checksum-conundrum^0|0|0|0|0|0|5|N|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@$O|18|P|19|1|1A]]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|A|$]]|$1|S|3|T|5|H|7|1C|8|@]|9|@]|A|$I|J]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

I wish MS SQL could also do something like COUNT(DISTINCT A, B). But it can't.

At first JayTee's answer seemed like a solution to me bu after some tests CHECKSUM() failed to create unique values. A quick example is, both CHECKSUM(31,467,519) and CHECKSUM(69,1120,823) gives the same answer which is 55.

Then I made some research and found that Microsoft does NOT recommend using CHECKSUM for change detection purposes. In some forums some suggested using 

<pre><code>SELECT COUNT(DISTINCT CHECKSUM(value1, value2, ..., valueN) + CHECKSUM(valueN, value(N-1), ..., value1))
</code></pre>

but this is also not conforting.

You can use HASHBYTES() function as suggested in <a href="https://stackoverflow.com/questions/12841331/tsql-checksum-conundrum">TSQL CHECKSUM conundrum</a>. However this also has a small chance of not returning unique results.

I would suggest using

<pre><code>SELECT COUNT(DISTINCT CAST(DocumentId AS VARCHAR)+'-'+CAST(DocumentSessionId AS VARCHAR)) FROM DocumentOutputItems
</code></pre>

blocks|key|1231656|text|当我在谷歌上搜索我自己的问题时，我发现了这一点，发现如果你计算不同的对象，你会得到正确的返回数字(我使用MySQL)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231657|SELECT+COUNT(DISTINCT+DocumentID)+AS+Count1,+
++COUNT(DISTINCT+DocumentSessionId)+AS+Count2
++FROM+DocumentOutputItems|code-block|syntax|javascript|1231658|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I found this when I Googled for my own issue, found that if you count DISTINCT objects, you get the correct number returned (I'm using MySQL)

<pre><code>SELECT COUNT(DISTINCT DocumentID) AS Count1, 
 COUNT(DISTINCT DocumentSessionId) AS Count2
 FROM DocumentOutputItems
</code></pre>

blocks|key|1446549|text|要作为单个查询运行，请连接列，然后获取连接字符串的实例的不同计数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446550|SELECT+count(DISTINCT+concat(DocumentId,+DocumentSessionId))+FROM+DocumentOutputItems;|code-block|syntax|javascript|1446551|在MySQL中，您可以在没有连接步骤的情况下执行相同的操作，如下所示：|1446552|SELECT+count(DISTINCT+DocumentId,+DocumentSessionId)+FROM+DocumentOutputItems;|1446553|MySQL文档中提到了此功能：|1446554|http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count-distinct|offset|length|1446555|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|2E|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|12|8|@]|9|@$O|13|P|14|1|15]]|A|$]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|N]]]]

To run as a single query, concatenate the columns, then get the distinct count of instances of the concatenated string.

<pre><code>SELECT count(DISTINCT concat(DocumentId, DocumentSessionId)) FROM DocumentOutputItems;
</code></pre>

In MySQL you can do the same thing without the concatenation step as follows:

<pre><code>SELECT count(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems;
</code></pre>

This feature is mentioned in the MySQL documentation:

<a href="http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count-distinct" rel="noreferrer">http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count-distinct</a>

blocks|key|1231701|text|这对我很管用。在oracle中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231702|SELECT+SUM(DECODE(COUNT(*),1,1,1))
FROM+DocumentOutputItems+GROUP+BY+DocumentId,+DocumentSessionId;|code-block|syntax|javascript|1231703|在jpql中：|1231704|SELECT+SUM(CASE+WHEN+COUNT(i)=1+THEN+1+ELSE+1+END)
FROM+DocumentOutputItems+i+GROUP+BY+i.DocumentId,+i.DocumentSessionId;|1231705|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

It works for me. In oracle:

<pre><code>SELECT SUM(DECODE(COUNT(*),1,1,1))
FROM DocumentOutputItems GROUP BY DocumentId, DocumentSessionId;
</code></pre>

In jpql:

<pre><code>SELECT SUM(CASE WHEN COUNT(i)=1 THEN 1 ELSE 1 END)
FROM DocumentOutputItems i GROUP BY i.DocumentId, i.DocumentSessionId;
</code></pre>

blocks|key|1446599|text|这样如何？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446600|Select+DocumentId,+DocumentSessionId,+count(*)+as+c+
from+DocumentOutputItems+
group+by+DocumentId,+DocumentSessionId;|code-block|syntax|javascript|1446601|这将为我们提供DocumentId和DocumentSessionId的所有可能组合的计数|1446602|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

How about this,

<pre><code>Select DocumentId, DocumentSessionId, count(*) as c 
from DocumentOutputItems 
group by DocumentId, DocumentSessionId;
</code></pre>

This will get us the count of all possible combinations of DocumentId, and DocumentSessionId

blocks|key|1231805|text|许多(大多数？)SQL数据库可以使用像值这样的元组，所以你可以这样做：SELECT+COUNT(DISTINCT+(DocumentId,+DocumentSessionId))+++++++FROM+DocumentOutputItems;如果你的数据库不支持这一点，它可以按照@oncel-umut-turer建议的校验和或其他提供良好唯一性的标量函数(如COUNT(DISTINCT+CONCAT(DocumentId,+':',+DocumentSessionId))+)来模拟。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1231806|元组的一个相关用法是执行IN查询，例如：SELECT+*+FROM+DocumentOutputItems+WHERE+(DocumentId,+DocumentSessionId)+in+(('a',+'1'),+('b',+'2'));|1231807|entityMap^0|Z|2E|50|1M|0|C|2|K|2S|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

Many (most?) SQL databases can work with tuples like values so you can just do:
<code>
SELECT COUNT(DISTINCT (DocumentId, DocumentSessionId))
 FROM DocumentOutputItems;
</code>
If your database doesn't support this, it can be simulated as per @oncel-umut-turer's suggestion of CHECKSUM or other scalar function providing good uniqueness e.g.
<code>
COUNT(DISTINCT CONCAT(DocumentId, ':', DocumentSessionId))</code>.

A related use of tuples is performing <code>IN</code> queries such as:
<code>
SELECT * FROM DocumentOutputItems
WHERE (DocumentId, DocumentSessionId) in (('a', '1'), ('b', '2'));
</code>

blocks|key|1446718|text|您可以只使用Count函数两次。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446719|在这种情况下，它将是：|1446720|SELECT+COUNT+(DISTINCT+DocumentId),+COUNT+(DISTINCT+DocumentSessionId)+
FROM+DocumentOutputItems|code-block|syntax|javascript|1446721|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can just use the Count Function Twice.

In this case, it would be:

<pre><code>SELECT COUNT (DISTINCT DocumentId), COUNT (DISTINCT DocumentSessionId) 
FROM DocumentOutputItems
</code></pre>

blocks|key|1446763|text|此代码使用distinct+on+2参数，并提供特定于这些distinct值row+count的行数。在MySQL中，它对我来说就像一个护身符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1446764|select+DISTINCT+DocumentId+as+i,++DocumentSessionId+as+s+,+count(*)+
from+DocumentOutputItems+++
group+by+i+,s;|code-block|syntax|javascript|1446765|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This code uses distinct on 2 parameters and provides count of number of rows specific to those distinct values row count. It worked for me in MySQL like a charm.

<pre><code>select DISTINCT DocumentId as i, DocumentSessionId as s , count(*) 
from DocumentOutputItems 
group by i ,s;
</code></pre>

blocks|key|1231972|text|我有一个类似的问题，但我的查询是一个子查询，其中包含主查询中的比较数据。类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231973|Select+code,+id,+title,+name+
(select+count(distinct+col1)+from+mytable+where+code+=+a.code+and+length(title)+>0)
from+mytable+a
group+by+code,+id,+title,+name
--needs+distinct+over+col2+as+well+as+col1|code-block|syntax|javascript|1231974|忽略其中的复杂性，我意识到我不能将a.code的值放入子查询中，而不能使用原始问题中描述的双重子查询|1231975|Select+count(1)+from+(select+distinct+col1,+col2+from+mytable+where+code+=+a.code...)
--this+doesn't+work+because+the+sub-query+doesn't+know+what+"a"+is|1231976|因此，我最终发现我可以作弊，并将这些列组合在一起：|1231977|Select+count(distinct(col1+%7C%7C+col2))+from+mytable+where+code+=+a.code...|1231978|这就是最终成功的方法。|1231979|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I had a similar question but the query I had was a sub-query with the comparison data in the main query. something like:

<pre><code>Select code, id, title, name 
(select count(distinct col1) from mytable where code = a.code and length(title) &gt;0)
from mytable a
group by code, id, title, name
--needs distinct over col2 as well as col1
</code></pre>

ignoring the complexities of this, I realized I couldn't get the value of a.code into the subquery with the double sub query described in the original question 

<pre><code>Select count(1) from (select distinct col1, col2 from mytable where code = a.code...)
--this doesn't work because the sub-query doesn't know what "a" is
</code></pre>

So eventually I figured out I could cheat, and combine the columns:

<pre><code>Select count(distinct(col1 || col2)) from mytable where code = a.code...
</code></pre>

This is what ended up working

blocks|key|1231987|text|我使用过这种方法，它对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1231988|SELECT+COUNT(DISTINCT+DocumentID+%7C%7C+DocumentSessionId)+
FROM++DocumentOutputItems|code-block|syntax|javascript|1231989|对于我的情况，它提供了正确的结果。|1231990|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I have used this approach and it has worked for me.

<pre><code>SELECT COUNT(DISTINCT DocumentID || DocumentSessionId) 
FROM DocumentOutputItems
</code></pre>

For my case, it provides correct result.

blocks|key|1232074|text|如果您正在处理固定长度的数据类型，您可以转换为binary，以便非常容易、非常快速地完成此操作。假设DocumentId和DocumentSessionId都是ints，因此是4字节长...|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1232075|SELECT+COUNT(DISTINCT+CAST(DocumentId+as+binary(4))+%2B+CAST(DocumentSessionId+as+binary(4)))
FROM+DocumentOutputItems|code-block|syntax|javascript|1232076|我的特定问题要求我将SUM除以各种外键和日期字段的不同组合的COUNT，按另一个外键分组，偶尔按某些值或键进行过滤。表非常大，使用子查询大大增加了查询时间。由于复杂性，统计根本不是一个可行的选择。CHECKSUM解决方案的转换速度也太慢了，特别是由于数据类型多种多样，我不能冒它不可靠的风险。|1232077|然而，使用上面的解决方案实际上没有增加查询时间(与简单地使用SUM相比)，并且应该是完全可靠的！它应该能够在类似的情况下帮助其他人，所以我把它贴在这里。|1232078|entityMap^0|N|6|1E|A|1P|H|28|3|0|0|A|3|U|5|2Q|8|0|U|3|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|P|$]]

If you're working with datatypes of fixed length, you can cast to <code>binary</code> to do this very easily and very quickly. Assuming <code>DocumentId</code> and <code>DocumentSessionId</code> are both <code>int</code>s, and are therefore 4 bytes long...

<pre><code>SELECT COUNT(DISTINCT CAST(DocumentId as binary(4)) + CAST(DocumentSessionId as binary(4)))
FROM DocumentOutputItems
</code></pre>

My specific problem required me to divide a <code>SUM</code> by the <code>COUNT</code> 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 <code>CHECKSUM</code> 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.

However, using the above solution had virtually no increase on the query time (comparing with using simply the <code>SUM</code>), and should be completely reliable! It should be able to help others in a similar situation so I'm posting it here.

Is there a better way of doing a query like this:

<pre><code>SELECT COUNT(*) 
FROM (SELECT DISTINCT DocumentId, DocumentSessionId
 FROM DocumentOutputItems) AS internalQuery
</code></pre>

I need to count the number of distinct items from this table but the distinct is over two columns.

My query works fine but I was wondering if I can get the final result using just one query (without using a sub-query)

Counting DISTINCT over multiple columns

SQL数据库

数据库

MySQL

有没有更好的方法来执行这样的查询：SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId      FROM DocumentOutputItems) AS internalQuery我需要计算这个表中的不同项的数量，但是distinct超...

问在多个列上计算DISTINCT
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在多个列上计算DISTINCTEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在多个列上计算DISTINCT
EN