在列列表中使用子查询

Question

我想创建一个查询来计算过去 7 天、14 天和 28 天创建的记录数。我的结果将返回类似以下内容的内容：

7Days  14Days  28Days  
21     35      56

我知道如何在每个时间（例如 7 天）内执行此操作，但我是否可以在一个查询中捕获所有三个？

select count(*) from Mytable
where Created > DATEADD(day,-8, getdate()) 

Answer 1

也不漂亮，但不依赖子查询（表/列名称来自 AdventureWorks）。如果 case 语句符合您的条件，则返回 1，否则返回 0 - 那么您只需对结果求和：

select sum(case when datediff(day, modifieddate, getdate()) <= 7
                then 1 else 0 end) as '7days',
       sum(case when datediff(day, modifieddate, getdate()) > 7
                     and datediff(day, modifieddate, getdate()) <= 14
                then 1 else 0 end) as '14days',
       sum(case when datediff(day, modifieddate, getdate()) > 14
                     and datediff(day, modifieddate, getdate()) <= 28
                then 1 else 0 end) as '28days'
from sales.salesorderdetail

编辑： 更新了 datediff 函数 - 按照其编写方式，它将返回负数（假设moddeddate 是过去的）导致所有项目都属于第一种情况。感谢 Andriy M 指出了这一点

Answer 2

它不是世界上最漂亮的代码，但它确实有效。尝试从三个子查询中进行选择，每个子查询对应一个范围。

select * from 
(select COUNT(*) as Cnt from    Log_UrlRewrites where CreateDate >= DATEADD(day, -7, getdate())) as Seven
inner join (select  COUNT(*) as Cnt from    Log_UrlRewrites where CreateDate >= DATEADD(day, -14, getdate())) as fourteen on 1 = 1
inner join (select  COUNT(*) as Cnt from    Log_UrlRewrites where CreateDate >= DATEADD(day, -28, getdate())) as twentyeight on 1 = 1

Answer 3

select
(
  select count(*)
  from Mytable
  where Created > DATEADD(day,-8, getdate())
) as [7Days],
(
  select count(*)
  from Mytable
  where Created > DATEADD(day,-15, getdate())
) as [14Days], 
(
  select count(*)
  from Mytable
  where Created > DATEADD(day,-29, getdate())
) as [28Days] 

Answer 4

SELECT
  [7Days]  = COUNT(CASE UtmostRange WHEN  7 THEN 1 END),
  [14Days] = COUNT(CASE UtmostRange WHEN 14 THEN 1 END),
  [28Days] = COUNT(CASE UtmostRange WHEN 28 THEN 1 END)
FROM (
  SELECT
    *,
    UtmostRange = CASE
      WHEN Created > DATEADD(day,  -8, GETDATE()) THEN  7
      WHEN Created > DATEADD(day, -15, GETDATE()) THEN 14
      WHEN Created > DATEADD(day, -29, GETDATE()) THEN 28
    END
  FROM Mytable
) s