时间戳

首页 > 解决方案 > SQL Count 案例，我有一个唯一字段

sql - SQL Count 案例，我有一个唯一字段

问题描述

一直在使用下面的代码来计算所有商店每天发生的次数。只是简单地计算 Site_Review_Date。

所以 1 家商店可能有 2 次或更多次出现

现在我每天都需要独特的商店出现。继续被难住所以寻求帮助。

数据示例

Store   Site_Review_Date
90      2021-03-02
90      2021-03-02
1       2021-03-02
3       2021-03-02
20      2021-03-02
87      2021-03-02

因此，如果我计算日期，我会在星期二寻找 5 而不是 6

Declare  @reportStartDate date

set @reportStartDate ='2021-1-03'


SELECT 
     
    DATEPART(wk,@reportStartDate ) as Week,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 0 THEN 1 END) AS S,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 1 THEN 1 END) AS M,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate,Site_Review_Date) = 2 THEN 1 END) AS T,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 3 THEN 1 END) AS W,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 4 THEN 1 END) AS R,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 5 THEN 1 END) AS F,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 6 THEN 1 END) AS Sat,
    COUNT(*) AS TOTAL
FROM dbo.Map_Survey_Tracking

WHERE Site_Review_Date BETWEEN @reportStartDate/*Sunday, report start date*/ AND DATEADD(dd, 7, @reportStartDate)

结果

Week    S   M   T   W   R   F   Sat TOTAL
2   0   4   5   5   10  9   6   42

标签： sqlcountcase

解决方案

我想你想要count(distinct)：

SELECT DATEPART(wk,@reportStartDate ) as Week,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 0 THEN Store END) AS S,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 1 THEN Store END) AS M,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate,Site_Review_Date) = 2 THEN Store END) AS T,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 3 THEN Store END) AS W,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 4 THEN Store END) AS R,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 5 THEN Store END) AS F,
       COUNT(DISTINCT CASE WHEN DATEDIFF(dd, @reportStartDate, Site_Review_Date) = 6 THEN Store END) AS Sat,
       COUNT(*) AS TOTAL
FROM dbo.Map_Survey_Tracking    
WHERE Site_Review_Date BETWEEN @reportStartDate/*Sunday, report start date*/ AND DATEADD(dd, 7, @reportStartDate)  ;

我注意到您正在使用DATEDIFF()一周中的某一天。 DATENAME()或者DATEPART()看起来更透明（尽管它们受全局设置的影响）。

推荐阅读