Server :从count()中删除重复项

Question

我正在Server数据库中创建报表。我将首先展示它的代码，然后描述它所做的事情和问题所在。

SELECT 
    COUNT(e.flowid) AS [count], 
    t.name AS [process], 
    CAST(DATEPART(YEAR, e.dtcr) AS VARCHAR) + '-' + CAST(RIGHT('0' + RTRIM(DATEPART(MONTH, e.dtcr)), 2) AS VARCHAR) + '-' + CAST(RIGHT('0' + RTRIM(DATEPART(DAY, e.dtcr)), 2) AS VARCHAR) AS [day]
FROM 
    dbo.[Event] e  
JOIN 
    dbo.Flow f ON e.flowid = f.id 
JOIN 
    dbo.WorkOrder o ON f.workorderno = o.number 
                    AND o.treenodeid IN (26067, 26152, 2469, 1815, 1913) -- only from requested processes 
JOIN 
    dbo.TreeNode t ON o.treenodeid = t.id -- for process name in select statement
JOIN  
    dbo.Product p ON f.productid = p.id
                  AND p.materialid NOT IN (26094, 27262, 27515, 27264, 28192, 28195, 26090, 26092, 26093, 27065, 26969, 27471, 28351, 28353, 28356, 28976, 27486, 29345, 29346, 27069, 28653, 28654, 26735, 26745, 28686) -- exclude unwanted family codes
WHERE 
    e.pass = 1 -- only passed units
    AND e.treenodeid IN (9036, 9037, 9038, 9039, 12594, 26330) -- only from requested events
    AND e.dtcr BETWEEN '2015-12-01 00:00:00.000' AND '2016-05-31 23:59:59.999' -- only from requested time interval 
GROUP BY 
    DATEPART(YEAR, e.dtcr), DATEPART(MONTH, e.dtcr), DATEPART(DAY, e.dtcr), t.name
ORDER BY 
    [day]

所做的查询是在一个时间段内传递特定事件的计数单元(带有一些过滤器)。

重要表格如下：

1. 事件--基本上是记录传递特定事件的单元。
2. 产品清单。

输出如下所示：

COUNT   PROCESS     DAY
71      Process-1   2015-12-01
1067    Process-2   2015-12-01
8       Process-3   2015-12-01
3       Process-4   2015-12-01
15      Process-1   2015-12-02
276     Process-2   2015-12-02
47      Process-3   2015-12-02
54      Process-4   2015-12-02

它做得很好，但也有一个问题。在某些特定的情况下，单元可以多次传递相同的事件，这个查询会对每一个这样的传递进行计数。我只需要数一次每个单位。

“复制”记录在事件表中。他们有不同的日期和身份证。对于所有的记录，我只需要计数一次是流动的。有什么简单的方法可以做到这一点吗？

谢谢你的时间和答案！

Answer 1

若要只计算每个流数一次，请执行count(distinct flowid)，即

SELECT 
    COUNT(distinct e.flowid) AS [count], 
    t.name AS [process], 
    CAST(DATEPART(YEAR, e.dtcr) AS VARCHAR) + '-' + CAST(RIGHT('0' + RTRIM(DATEPART(MONTH, e.dtcr)), 2) AS VARCHAR) + '-' + CAST(RIGHT('0' + RTRIM(DATEPART(DAY, e.dtcr)), 2) AS VARCHAR) AS [day]
FROM
...

Answer 2

听起来好像你需要第一次有什么东西超过门槛。您可以第一次使用row_number()。对于查询上的附加条件，这可能是很棘手的。这种修改可能对您有用：

select sum(case when seqnum = 1 then 1 else 0 end) as cnt,
       . . .
from (select e.*,
             row_number() over (partition by eventid order by e.dtcr) as seqnum
      from event e
      where e.pass = 1 and  -- only passed units
            e.treenodeid IN (9036, 9037, 9038, 9039, 12594, 26330) and
            e.dtcr >= '2015-12-01' AND e.dtcr < '2016-06-01'
     ) e join 
     . . .

您没有指定如何为重复的事件标识相同的事件。上面的用户使用eventid来实现这个目的。