如何在sqlserver中使用WITH子句获得24小时

问题描述

我创建了一个创建24小时的表变量，但我想创建返回24小时的CTE而不是表变量，因此请帮助如何从with子句中获取24小时.在这里，我的要求是比较表变量和源表之间按小时发送的订单，如果在特定时间(如上午10点)没有发送任何订单，则结果应该为0，这是从表变量返回的值，这是我正在使用左连接的原因.在这里，我的要求是代替表变量如何创建CTE.在这里，我给出了一些返回24小时的示例查询.

Hi,

i have created a table variable that creates 24 hours but instead of table variable i want to create CTE that returns 24 hours so please help how to get 24 hours from with clause. Here my requirement is comparing hourswise orders shipped between table variable and source table if on a particular hour like 10am on no orders shipped then result should be 0 this is return from table variable for this i am using left join. Here my requirement is instead of table variable how to create CTE. Here i am giving some sample query that returns 24 hours.

Declare @Hours Table (Hour int);
Declare @i as int;
Set @i = 5
While @i < 24
Begin
   Insert into @Hours
   Values(@i);

   Set @i = @i + 1;
End;

推荐答案

 WITH hourscte AS(
    SELECT 5 Hour
      UNION ALL
    SELECT Hour + 1
     FROM hourscte
     WHERE Hour  < 24
 )

SELECT * FROM hourscte
OPTION (MAXRECURSION 0)

如果要显示某个范围内的日期，请通过链接

http://www.mindfiresolutions.com/Display-Dates-In-A-Range- 1891.php [ ^ ]

If you want to display dates in a range, then go through the link

http://www.mindfiresolutions.com/Display-Dates-In-A-Range-1891.php[^]

这篇关于如何在sqlserver中使用WITH子句获得24小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！