SQL Server:从count()中删除重复项
问题描述:
我在SQL Server数据库中创建报告。我将首先显示它的代码,然后描述它的作用和问题。SQL Server:从count()中删除重复项
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]
查询所做的是计数在某个时间段内通过特定事件(使用某些过滤器)的单位。
重要的表是:
- 事件 - 基本日志传递特定事件的单位。
- 产品 - 单位列表。
输出是这样的:
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
它确实很好,但有一个问题。在某些特定情况下,单元可以多次传递相同的事件,并且此查询计算每次这样的传递。我只需要统计每个单位一次。
“重复”记录位于事件表中。他们有不同的日期和ID。我只需要计数一次的所有记录都是一样的。有没有简单的方法来实现这一点?
谢谢你的时间和答案!
答
要计算每个流ID只有一次,做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
...
答
这听起来像你第一次需要通过某个阈值。您可以第一次使用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
。
你的意思'计数(不同的流ID)'? – jarlh
你是认真的吗,真的那么简单吗? (是的,我尝试过,现在我觉得自己像个白痴。)请将它添加为答案,以便我可以将其标记为正确的。 – Andrew