在列列表中使用子查询
问题描述:
我想创建一个查询,以计算在过去的7,14和28天内创建了多少条记录。我的结果会返回类似于:在列列表中使用子查询
7Days 14Days 28Days
21 35 56
我知道如何为每个时间安排例如7天,但我可以在一个查询中捕获所有三个?
select count(*) from Mytable
where Created > DATEADD(day,-8, getdate())
答
而且不漂亮,但不依赖于子(表/列名来自AdventureWorks中)。如果它属于你的标准内的情况下语句返回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函数 - 它是书面的方式,它会返回一个负数(假设修改日期过去)导致所有项目属于第一种情况。感谢舍甫琴科M代表指出了这一点
答
这是不是世界上最漂亮的代码,但它的伎俩。尝试从三个子查询中进行选择,每个范围一个。
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
答
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]
答
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
这是做到这一点的最有效方法。没有理由编写子查询,而且速度非常慢。这个查询对数据进行一次传递,非常快速和高效。 – Brettski 2011-05-17 04:16:30
为了获得更好的性能,您应该在Created字段上创建索引,而不是'datediff(day,getdate(),Created) Dalex 2011-05-17 06:20:04
'modifieddate'应该早于还是晚于'GETDATE()'? – 2011-05-17 12:24:11