SQL Server 2008中多个列中的COUNT(DISTINCT) [英] COUNT(DISTINCT) in multiple columns in SQL Server 2008

问题描述

在Oracle中，可以使用 || 运算符(根据

In Oracle, it's possible to get a count of distinct values in multiple columns by using the || operator (according to this forum post, anyway):

SELECT COUNT(DISTINCT ColumnA || ColumnB) FROM MyTable

在SQL Server 2008中有没有办法做到这一点?我正在尝试执行一个查询以返回一些组统计信息，但是我似乎做不到.

Is there a way to do this in SQL Server 2008? I'm trying to perform a single query to return some group statistics, but I can't seem to do it.

例如，这是我要查询的值表:

For example, here is a table of values I'm trying to query:

AssetId MyId    TheirId   InStock
328     10      10        1
328     20      20        0
328     30      30        0
328     40      10        0
328     10      10        0
328     10      10        0
328     10      10        0
328     10      10        0

对于AssetId#328，我想计算 MyId 和 TheirId 列(4 = 10、20、30、40)中的唯一ID总数，以及 InStock 列(1)中非零行的总数:

For AssetId #328, I want to compute the total number of unique IDs in the MyId and TheirId columns (4 = 10, 20, 30, 40), as well as the total number of non-zero rows in the InStock column (1):

AssetId     TotalIds    AvailableIds
328         4           1

有办法以某种方式处理这种魔法吗?

Is there a way to work this magic somehow?

推荐答案

您可以使用 cross apply 和 values .

select T1.AssetId,
       count(distinct T2.ID) TotalIds,
       sum(case T2.InStock when 0 then 0 else 1 end) AvailableIds 
from YourTable as T1
  cross apply(values(T1.MyId, T1.InStock),
                    (T1.TheirId, 0)
             ) as T2(ID, InStock)
group by T1.AssetId  

SE数据

或者您可以在子查询中执行全部联合.

Or you can do a union all in a sub query.

select T.AssetId,
       count(distinct T.ID) TotalIds,
       sum(case T.InStock when 0 then 0 else 1 end) AvailableIds 
from (
     select AssetId, MyId as ID, InStock
     from YourTable
     union all
     select AssetID, TheirId, 0
     from YourTable
     ) as T
group by T.AssetId  

这篇关于SQL Server 2008中多个列中的COUNT(DISTINCT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！