Server:多列的唯一值之和

Question

使用Server，我有一个表，如下面的示例表所示。我需要每个列"BookOrder、StationaryOrder和打印顺序“的所有唯一值之和。

样本表：

KeyIDCustomer   BooksOrder  StationaryOrder PrintingOrder
29945843         1070756    1891514            198876
29945843         1070756    1893827            198876
29945843         1070758    1891514            198876
29945843         1070758    1893827            198876

我正在使用下面的编码来实现这个目标。

Select DISTINCT KeyIDCustomerID,
Sum(Case when BooksOrder is not null then 1 else 0 End) TotalBookOrders,  
Sum(Case when StationaryOrder is not null then 1 else 0 End) TotalStationaryOrder,
Sum(Case when PrintingOrder is not null then 1 else 0 End)TotalPrintingOrder

在am中使用此代码将得到如下结果

  KeyIDCustomerID   TotalBookOrders TotalStationaryOrder    TotalPrintingOrder
   29945843                    4                       4                    4

我希望结果是这样的

  KeyIDCustomerID   TotalBookOrders TotalStationaryOrder    TotalPrintingOrder
   29945843                    2                       2                    1

有什么方法可以在SQL中实现这个目标吗？

谢谢

Answer 1

做一个group by，count(distinct column)来计数：

Select KeyIDCustomerID,
       COUNT(distinct BooksOrder) TotalBookOrders,  
       COUNT(distinct StationaryOrder) TotalStationaryOrder,
       COUNT(distinct PrintingOrder) TotalPrintingOrder
from tablename
group by KeyIDCustomerID

Answer 2

我认为合适的术语是

每列的所有唯一值之和

是“唯一价值的计数”

COUNT (DISTINCT column_name)返回column_name中唯一的、非空值的数目。

Select 
KeyIDCustomerID,
COUNT(DISTINCT BooksOrder) as TotalBookOrders,  
COUNT(DISTINCT StationaryOrder) as TotalStationaryOrder,
COUNT(DISTINCT PrintingOrder) as TotalPrintingOrder
FROM SAMPLE_TABLE
GROUP BY KeyIDCustomerID