使用递归公用表表达式（如 while 循环）

发布于 2024-11-24 03:34:14 字数 989 浏览 0 评论 0原文

我有下表

   Log

 Date            date
 Description     varchar
 ID              integer

给定日期作为参数，我必须找到编号。或从开始日期到其后一个月使用递归 cte 每天记录的日志计数。有些天可能没有任何日志，所以我必须将计数打印为 0。

例如：

  select * from Log

  1   insert      2011-01-17
  2   blah blah   2011-01-23
  3   blah        2011-07-07

For 2011-01-17 作为输入，输出应该是

   2011-01-17    1
   2011-01-18    0
   2011-01-19    0
   ....
   2011-01-23    1
   .....
   2011-02-17    0

我必须使用递归 cte 来做到这一点.我不知道如何在每次递归中将日期加 1 以及如何停止\终止递归。

这是我到目前为止所做的事情：

 with cte as    (
    select '2011-01-17' as dat,count(*) as count 
from log group by date 
having date='2011-01-17' 

union all 

select dateadd(day,1,dat) as dat,count(*) as count 
from log,cte 
group by date 
having date=dateadd(day,1,dat) 
where dat<'2011-02-17' 
) 
select * from cte

原文

i have the following table

   Log

 Date            date
 Description     varchar
 ID              integer

Given a date as a parameter,i have to find the no. or count of logs logged each day from start date to a month after it using recursive cte.
Some Days may have not any logs,so i have to print the count as 0.

ex:

  select * from Log

returns

  1   insert      2011-01-17
  2   blah blah   2011-01-23
  3   blah        2011-07-07

For 2011-01-17 as input,the output should be

   2011-01-17    1
   2011-01-18    0
   2011-01-19    0
   ....
   2011-01-23    1
   .....
   2011-02-17    0

I have to use recursive cte to do that.i don't know how to increment the date by 1 in each recursion and how to stop\terminate the recursion.

This is the thing i have done so far:

 with cte as    (
    select '2011-01-17' as dat,count(*) as count 
from log group by date 
having date='2011-01-17' 

union all 

select dateadd(day,1,dat) as dat,count(*) as count 
from log,cte 
group by date 
having date=dateadd(day,1,dat) 
where dat<'2011-02-17' 
) 
select * from cte

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

九局 2024-12-01 03:34:15

WITH    days (d, maxd) AS
        (
        SELECT  MIN(date), MAX(date)
        FROM    log
        UNION ALL
        SELECT  DATEADD(day, 1, d), maxd
        FROM    days
        WHERE   d < maxd
        )
SELECT  d, COUNT(id)
FROM    days
LEFT JOIN
        log l
ON      l.date = d
GROUP BY
        d

WITH    days (d, maxd) AS
        (
        SELECT  MIN(date), MAX(date)
        FROM    log
        UNION ALL
        SELECT  DATEADD(day, 1, d), maxd
        FROM    days
        WHERE   d < maxd
        )
SELECT  d, COUNT(id)
FROM    days
LEFT JOIN
        log l
ON      l.date = d
GROUP BY
        d

回复收藏 0 原文

~没有更多了~