使用SQL统计某个时间某个状态的案例

Question

我需要开发一个查询来计算每月“未结”案件的总数。 我有一个带有 id 和名称的“cases”表，以及一个带有日期时间列、caseid 列和状态的“state_changes”表。

如何计算每个月过去有状态为“开放”的记录但没有相应的状态为“关闭”的记录的案例数量？

我正在使用 SQL Server 2000。

Answer 1

为打开事件创建一个状态更改表查询，为关闭事件创建一个状态更改表查询。

创建一个查询，对案例 ID 进行打开到关闭的外连接，返回两个查询的案例 ID

查询后一个查询结果，以查找“关闭”事件查询中的 ID 为 null 的

行数后者的查询结果。

非常粗略地类似于（我的脑海中，没有更正）：

SELECT COUNT (T1.CaseID) FROM (SELECT T1.CaseID AS T1_CaseID, T2.CaseID AS T2_CaseID 
FROM ((SELECT CaseID FROM state_changes WHERE state = 'open' AND timestamp BETWEEN 1-Jan-09 AND 30-Jan-09) AS T1 OUTER JOIN (SELECT CaseID FROM state_changes WHERE state = 'closed' AND timestamp BETWEEN 1-Jan-09 AND 30-Jan-09) AS T2 ON T1.CaseID = T2.CaseID)) WHERE T2_CaseID = NULL

Answer 2

这应该会让您接近（T-SQL）：

SELECT
  MONTH(s.casedate) m,
  YEAR(s.casedate)  y,
  COUNT(DISTINCT c.caseid)   count_cases
FROM
  cases c
  INNER JOIN state_changes s ON s.caseid = c.caseid
WHERE
  s.state = 'open'           /* "with state 'open'" */
  AND s.casedate < GETDATE() /* "in the past" */
  AND NOT EXISTS (           /* "without corresp. record with state 'closed'" */
    SELECT 1 FROM state_changes i WHERE i.caseid = s.caseid AND i.state = 'closed'
  )
GROUP BY
  MONTH(s.casedate),
  YEAR(s.casedate)

编辑：要对所有十二个月进行统计（独立于这些月份中存在的实际案例），您需要一个小的辅助表（我们称之为月） ，它只包含一列（我们也称其为月份），其中的数字为 1 到 12。然后您将其加入：

SELECT
  m.month,
  COUNT(DISTINCT c.caseid) count_cases
FROM
  cases c
  INNER JOIN state_changes s ON s.caseid = c.caseid
  LEFT  JOIN month         m ON m.month  = MONTH(s.casedate)
WHERE
  s.state = 'open'
  AND YEAR(c.createddate) = YEAR(GETDATE())  /* whatever */
  AND NOT EXISTS (
    SELECT 1 FROM state_changes i WHERE i.caseid = s.caseid AND i.state = 'closed'
  )
GROUP BY
  m.month
ORDER BY
  m.month