求一条SQL语句

大力水手 2011-05-23 09:48:43 
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';

with dates(day_no,hour_no,min_no) as (

select @begin_date as day_no,0 as hour_no,0 as min_no

union 

select @begin_date as day_no,0 as hour_no,1 as min_no

union all

select dateadd(day,1,day_no),hour_no,min_no from dates

where day_no<@end_date and hour_no<=23

union all

select day_no,hour_no+1,min_no from dates

where  hour_no<=22 and  day_no<@end_date

)



select  day_no,hour_no,min_no  from dates order by day_no,hour_no,min_no


这样写为什么不对...我想要的结果是:输入两个日期..一个开始日期一个结束日期...开始日期0点对应一个0,1 1点对应一个0,1 这样循环下去 最好用with 因为我自己能用循环写出来 谢谢...结贴给第一个对的人





2011-01-02 00:00:00.000 0 0
2011-01-02 00:00:00.000 0 1
2011-01-02 00:00:00.000 1 0
2011-01-02 00:00:00.000 1 1
2011-01-02 00:00:00.000 2 0
2011-01-02 00:00:00.000 2 1
2011-01-02 00:00:00.000 3 0
2011-01-02 00:00:00.000 3 1
2011-01-02 00:00:00.000 4 0
2011-01-02 00:00:00.000 4 1
2011-01-02 00:00:00.000 5 0
2011-01-02 00:00:00.000 5 1
...全文
88 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
大力水手 2011-05-23
  • 打赏
  • 举报
 回复
谢谢...呵呵..麻烦了啊
-晴天 2011-05-23
  • 打赏
  • 举报
 回复 
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';

with cte as(

select @begin_date dt,0 h,0 m

union all

select dateadd(d,(h+m)/24,dt),(h+m)%24,(m+1)%2 from cte where dateadd(d,(h+m)/24,dt)<@end_date

)select * from cte 

OPTION (MAXRECURSION 0);

go

/*

dt                      h           m

----------------------- ----------- -----------

2011-01-02 00:00:00.000 0           0

2011-01-02 00:00:00.000 0           1

2011-01-02 00:00:00.000 1           0

2011-01-02 00:00:00.000 1           1

2011-01-02 00:00:00.000 2           0

2011-01-02 00:00:00.000 2           1

2011-01-02 00:00:00.000 3           0

2011-01-02 00:00:00.000 3           1

2011-01-02 00:00:00.000 4           0

2011-01-02 00:00:00.000 4           1

2011-01-02 00:00:00.000 5           0

2011-01-02 00:00:00.000 5           1

2011-01-02 00:00:00.000 6           0

2011-01-02 00:00:00.000 6           1

2011-01-02 00:00:00.000 7           0

2011-01-02 00:00:00.000 7           1

2011-01-02 00:00:00.000 8           0

2011-01-02 00:00:00.000 8           1

2011-01-02 00:00:00.000 9           0

2011-01-02 00:00:00.000 9           1

2011-01-02 00:00:00.000 10          0

2011-01-02 00:00:00.000 10          1

2011-01-02 00:00:00.000 11          0

2011-01-02 00:00:00.000 11          1

2011-01-02 00:00:00.000 12          0

2011-01-02 00:00:00.000 12          1

2011-01-02 00:00:00.000 13          0

2011-01-02 00:00:00.000 13          1

2011-01-02 00:00:00.000 14          0

2011-01-02 00:00:00.000 14          1

2011-01-02 00:00:00.000 15          0

2011-01-02 00:00:00.000 15          1

2011-01-02 00:00:00.000 16          0

2011-01-02 00:00:00.000 16          1

2011-01-02 00:00:00.000 17          0

2011-01-02 00:00:00.000 17          1

2011-01-02 00:00:00.000 18          0

2011-01-02 00:00:00.000 18          1

2011-01-02 00:00:00.000 19          0

2011-01-02 00:00:00.000 19          1

2011-01-02 00:00:00.000 20          0

2011-01-02 00:00:00.000 20          1

2011-01-02 00:00:00.000 21          0

2011-01-02 00:00:00.000 21          1

2011-01-02 00:00:00.000 22          0

2011-01-02 00:00:00.000 22          1

2011-01-02 00:00:00.000 23          0

2011-01-02 00:00:00.000 23          1

2011-01-03 00:00:00.000 0           0

2011-01-03 00:00:00.000 0           1

2011-01-03 00:00:00.000 1           0

2011-01-03 00:00:00.000 1           1

2011-01-03 00:00:00.000 2           0

2011-01-03 00:00:00.000 2           1

2011-01-03 00:00:00.000 3           0

2011-01-03 00:00:00.000 3           1

2011-01-03 00:00:00.000 4           0

2011-01-03 00:00:00.000 4           1

2011-01-03 00:00:00.000 5           0

2011-01-03 00:00:00.000 5           1

2011-01-03 00:00:00.000 6           0

2011-01-03 00:00:00.000 6           1

2011-01-03 00:00:00.000 7           0

2011-01-03 00:00:00.000 7           1

2011-01-03 00:00:00.000 8           0

2011-01-03 00:00:00.000 8           1

2011-01-03 00:00:00.000 9           0

2011-01-03 00:00:00.000 9           1

2011-01-03 00:00:00.000 10          0

2011-01-03 00:00:00.000 10          1

2011-01-03 00:00:00.000 11          0

2011-01-03 00:00:00.000 11          1

2011-01-03 00:00:00.000 12          0

2011-01-03 00:00:00.000 12          1

2011-01-03 00:00:00.000 13          0

2011-01-03 00:00:00.000 13          1

2011-01-03 00:00:00.000 14          0

2011-01-03 00:00:00.000 14          1

2011-01-03 00:00:00.000 15          0

2011-01-03 00:00:00.000 15          1

2011-01-03 00:00:00.000 16          0

2011-01-03 00:00:00.000 16          1

2011-01-03 00:00:00.000 17          0

2011-01-03 00:00:00.000 17          1

2011-01-03 00:00:00.000 18          0

2011-01-03 00:00:00.000 18          1

2011-01-03 00:00:00.000 19          0

2011-01-03 00:00:00.000 19          1

2011-01-03 00:00:00.000 20          0

2011-01-03 00:00:00.000 20          1

2011-01-03 00:00:00.000 21          0

2011-01-03 00:00:00.000 21          1

2011-01-03 00:00:00.000 22          0

2011-01-03 00:00:00.000 22          1

2011-01-03 00:00:00.000 23          0

2011-01-03 00:00:00.000 23          1



(96 行受影响)



*/
大力水手 2011-05-23
  • 打赏
  • 举报
 回复
把低97 行去掉 分给你
-晴天 2011-05-23
  • 打赏
  • 举报
 回复
用递归做:
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';

with cte as(

select @begin_date dt,0 h,0 m

union all

select dateadd(d,(h+m)/24,dt),(h+m)%24,(m+1)%2 from cte where dt<@end_date

)select * from cte 

OPTION (MAXRECURSION 0);

go

/*

dt                      h           m

----------------------- ----------- -----------

2011-01-02 00:00:00.000 0           0

2011-01-02 00:00:00.000 0           1

2011-01-02 00:00:00.000 1           0

2011-01-02 00:00:00.000 1           1

2011-01-02 00:00:00.000 2           0

2011-01-02 00:00:00.000 2           1

2011-01-02 00:00:00.000 3           0

2011-01-02 00:00:00.000 3           1

2011-01-02 00:00:00.000 4           0

2011-01-02 00:00:00.000 4           1

2011-01-02 00:00:00.000 5           0

2011-01-02 00:00:00.000 5           1

2011-01-02 00:00:00.000 6           0

2011-01-02 00:00:00.000 6           1

2011-01-02 00:00:00.000 7           0

2011-01-02 00:00:00.000 7           1

2011-01-02 00:00:00.000 8           0

2011-01-02 00:00:00.000 8           1

2011-01-02 00:00:00.000 9           0

2011-01-02 00:00:00.000 9           1

2011-01-02 00:00:00.000 10          0

2011-01-02 00:00:00.000 10          1

2011-01-02 00:00:00.000 11          0

2011-01-02 00:00:00.000 11          1

2011-01-02 00:00:00.000 12          0

2011-01-02 00:00:00.000 12          1

2011-01-02 00:00:00.000 13          0

2011-01-02 00:00:00.000 13          1

2011-01-02 00:00:00.000 14          0

2011-01-02 00:00:00.000 14          1

2011-01-02 00:00:00.000 15          0

2011-01-02 00:00:00.000 15          1

2011-01-02 00:00:00.000 16          0

2011-01-02 00:00:00.000 16          1

2011-01-02 00:00:00.000 17          0

2011-01-02 00:00:00.000 17          1

2011-01-02 00:00:00.000 18          0

2011-01-02 00:00:00.000 18          1

2011-01-02 00:00:00.000 19          0

2011-01-02 00:00:00.000 19          1

2011-01-02 00:00:00.000 20          0

2011-01-02 00:00:00.000 20          1

2011-01-02 00:00:00.000 21          0

2011-01-02 00:00:00.000 21          1

2011-01-02 00:00:00.000 22          0

2011-01-02 00:00:00.000 22          1

2011-01-02 00:00:00.000 23          0

2011-01-02 00:00:00.000 23          1

2011-01-03 00:00:00.000 0           0

2011-01-03 00:00:00.000 0           1

2011-01-03 00:00:00.000 1           0

2011-01-03 00:00:00.000 1           1

2011-01-03 00:00:00.000 2           0

2011-01-03 00:00:00.000 2           1

2011-01-03 00:00:00.000 3           0

2011-01-03 00:00:00.000 3           1

2011-01-03 00:00:00.000 4           0

2011-01-03 00:00:00.000 4           1

2011-01-03 00:00:00.000 5           0

2011-01-03 00:00:00.000 5           1

2011-01-03 00:00:00.000 6           0

2011-01-03 00:00:00.000 6           1

2011-01-03 00:00:00.000 7           0

2011-01-03 00:00:00.000 7           1

2011-01-03 00:00:00.000 8           0

2011-01-03 00:00:00.000 8           1

2011-01-03 00:00:00.000 9           0

2011-01-03 00:00:00.000 9           1

2011-01-03 00:00:00.000 10          0

2011-01-03 00:00:00.000 10          1

2011-01-03 00:00:00.000 11          0

2011-01-03 00:00:00.000 11          1

2011-01-03 00:00:00.000 12          0

2011-01-03 00:00:00.000 12          1

2011-01-03 00:00:00.000 13          0

2011-01-03 00:00:00.000 13          1

2011-01-03 00:00:00.000 14          0

2011-01-03 00:00:00.000 14          1

2011-01-03 00:00:00.000 15          0

2011-01-03 00:00:00.000 15          1

2011-01-03 00:00:00.000 16          0

2011-01-03 00:00:00.000 16          1

2011-01-03 00:00:00.000 17          0

2011-01-03 00:00:00.000 17          1

2011-01-03 00:00:00.000 18          0

2011-01-03 00:00:00.000 18          1

2011-01-03 00:00:00.000 19          0

2011-01-03 00:00:00.000 19          1

2011-01-03 00:00:00.000 20          0

2011-01-03 00:00:00.000 20          1

2011-01-03 00:00:00.000 21          0

2011-01-03 00:00:00.000 21          1

2011-01-03 00:00:00.000 22          0

2011-01-03 00:00:00.000 22          1

2011-01-03 00:00:00.000 23          0

2011-01-03 00:00:00.000 23          1

2011-01-04 00:00:00.000 0           0



(97 行受影响)



*/
-晴天 2011-05-23
  • 打赏
  • 举报
 回复 
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';

select convert(varchar(10),dt,120)d,convert(int,convert(varchar(2),dt,108))h,convert(int,right(convert(varchar(5),dt,108),2))/30 hm from(

select dateadd(mi,number*30,@begin_date)dt from master..spt_values where type='p' and dateadd(mi,number*30,@begin_date)<=@end_date

)t

/*

d          h           hm

---------- ----------- -----------

2011-01-02 0           0

2011-01-02 0           1

2011-01-02 1           0

2011-01-02 1           1

2011-01-02 2           0

2011-01-02 2           1

2011-01-02 3           0

2011-01-02 3           1

2011-01-02 4           0

2011-01-02 4           1

2011-01-02 5           0

2011-01-02 5           1

2011-01-02 6           0

2011-01-02 6           1

2011-01-02 7           0

2011-01-02 7           1

2011-01-02 8           0

2011-01-02 8           1

2011-01-02 9           0

2011-01-02 9           1

2011-01-02 10          0

2011-01-02 10          1

2011-01-02 11          0

2011-01-02 11          1

2011-01-02 12          0

2011-01-02 12          1

2011-01-02 13          0

2011-01-02 13          1

2011-01-02 14          0

2011-01-02 14          1

2011-01-02 15          0

2011-01-02 15          1

2011-01-02 16          0

2011-01-02 16          1

2011-01-02 17          0

2011-01-02 17          1

2011-01-02 18          0

2011-01-02 18          1

2011-01-02 19          0

2011-01-02 19          1

2011-01-02 20          0

2011-01-02 20          1

2011-01-02 21          0

2011-01-02 21          1

2011-01-02 22          0

2011-01-02 22          1

2011-01-02 23          0

2011-01-02 23          1

2011-01-03 0           0

2011-01-03 0           1

2011-01-03 1           0

2011-01-03 1           1

2011-01-03 2           0

2011-01-03 2           1

2011-01-03 3           0

2011-01-03 3           1

2011-01-03 4           0

2011-01-03 4           1

2011-01-03 5           0

2011-01-03 5           1

2011-01-03 6           0

2011-01-03 6           1

2011-01-03 7           0

2011-01-03 7           1

2011-01-03 8           0

2011-01-03 8           1

2011-01-03 9           0

2011-01-03 9           1

2011-01-03 10          0

2011-01-03 10          1

2011-01-03 11          0

2011-01-03 11          1

2011-01-03 12          0

2011-01-03 12          1

2011-01-03 13          0

2011-01-03 13          1

2011-01-03 14          0

2011-01-03 14          1

2011-01-03 15          0

2011-01-03 15          1

2011-01-03 16          0

2011-01-03 16          1

2011-01-03 17          0

2011-01-03 17          1

2011-01-03 18          0

2011-01-03 18          1

2011-01-03 19          0

2011-01-03 19          1

2011-01-03 20          0

2011-01-03 20          1

2011-01-03 21          0

2011-01-03 21          1

2011-01-03 22          0

2011-01-03 22          1

2011-01-03 23          0

2011-01-03 23          1

2011-01-04 0           0



(97 行受影响)

*/
百年树人 2011-05-23
  • 打赏
  • 举报
 回复 
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';



select *

from

(

select dateadd(hh,number,@begin_date) as day_no

from master..spt_values 

where type='P' and dateadd(hh,number,@begin_date)<@end_date

) a

join

(select 0 as hour_no,0 as min_no

union 

select 0 as hour_no,1 as min_no

) b on 1=1

order by day_no,hour_no,min_no
-晴天 2011-05-23
  • 打赏
  • 举报
 回复 
declare @begin_date datetime,@end_date datetime 

select @begin_date='20110102',@end_date='20110104';

select dateadd(mi,number*30,@begin_date) from master..spt_values where type='p' and dateadd(mi,number*30,@begin_date)<=@end_date

/*

-----------------------

2011-01-02 00:00:00.000

2011-01-02 00:30:00.000

2011-01-02 01:00:00.000

2011-01-02 01:30:00.000

2011-01-02 02:00:00.000

2011-01-02 02:30:00.000

2011-01-02 03:00:00.000

2011-01-02 03:30:00.000

2011-01-02 04:00:00.000

2011-01-02 04:30:00.000

2011-01-02 05:00:00.000

2011-01-02 05:30:00.000

2011-01-02 06:00:00.000

2011-01-02 06:30:00.000

2011-01-02 07:00:00.000

2011-01-02 07:30:00.000

2011-01-02 08:00:00.000

2011-01-02 08:30:00.000

2011-01-02 09:00:00.000

2011-01-02 09:30:00.000

2011-01-02 10:00:00.000

2011-01-02 10:30:00.000

2011-01-02 11:00:00.000

2011-01-02 11:30:00.000

2011-01-02 12:00:00.000

2011-01-02 12:30:00.000

2011-01-02 13:00:00.000

2011-01-02 13:30:00.000

2011-01-02 14:00:00.000

2011-01-02 14:30:00.000

2011-01-02 15:00:00.000

2011-01-02 15:30:00.000

2011-01-02 16:00:00.000

2011-01-02 16:30:00.000

2011-01-02 17:00:00.000

2011-01-02 17:30:00.000

2011-01-02 18:00:00.000

2011-01-02 18:30:00.000

2011-01-02 19:00:00.000

2011-01-02 19:30:00.000

2011-01-02 20:00:00.000

2011-01-02 20:30:00.000

2011-01-02 21:00:00.000

2011-01-02 21:30:00.000

2011-01-02 22:00:00.000

2011-01-02 22:30:00.000

2011-01-02 23:00:00.000

2011-01-02 23:30:00.000

2011-01-03 00:00:00.000

2011-01-03 00:30:00.000

2011-01-03 01:00:00.000

2011-01-03 01:30:00.000

2011-01-03 02:00:00.000

2011-01-03 02:30:00.000

2011-01-03 03:00:00.000

2011-01-03 03:30:00.000

2011-01-03 04:00:00.000

2011-01-03 04:30:00.000

2011-01-03 05:00:00.000

2011-01-03 05:30:00.000

2011-01-03 06:00:00.000

2011-01-03 06:30:00.000

2011-01-03 07:00:00.000

2011-01-03 07:30:00.000

2011-01-03 08:00:00.000

2011-01-03 08:30:00.000

2011-01-03 09:00:00.000

2011-01-03 09:30:00.000

2011-01-03 10:00:00.000

2011-01-03 10:30:00.000

2011-01-03 11:00:00.000

2011-01-03 11:30:00.000

2011-01-03 12:00:00.000

2011-01-03 12:30:00.000

2011-01-03 13:00:00.000

2011-01-03 13:30:00.000

2011-01-03 14:00:00.000

2011-01-03 14:30:00.000

2011-01-03 15:00:00.000

2011-01-03 15:30:00.000

2011-01-03 16:00:00.000

2011-01-03 16:30:00.000

2011-01-03 17:00:00.000

2011-01-03 17:30:00.000

2011-01-03 18:00:00.000

2011-01-03 18:30:00.000

2011-01-03 19:00:00.000

2011-01-03 19:30:00.000

2011-01-03 20:00:00.000

2011-01-03 20:30:00.000

2011-01-03 21:00:00.000

2011-01-03 21:30:00.000

2011-01-03 22:00:00.000

2011-01-03 22:30:00.000

2011-01-03 23:00:00.000

2011-01-03 23:30:00.000

2011-01-04 00:00:00.000



(97 行受影响)





*/
怎么在数据库中同时执行两条sql语句并且当一条语句报错另一条语句就不再执行？
这个是啥我也忘了，反正是这样写的： set autocommit=0; BEGIN; sql语句 ROLLBACK;/commit; set autocommit=0;这个东西必须得要的，因为它可以帮助你关闭一个自动执行的操作，要是不加的话，代码很容易出错，因为我试过 ROLLBACK;//回轮(执行时返回两条sql语句或多条sql语句，并不执行两条或多条sql语句) commit;//提交(执行时如果一条语句出错其他的语句将不再执行，停止执行) 实用于银行转账，扣钱时对方账户里加钱，这个操作
一条SQL语句在MySQL中执行过程全解析
本篇文章会分析一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。
一条 SQL 语句是如何执行的
sql 语句执行流程
mysql 删除一条数据sql语句_sql删除语句
sql 删除语句一般简单的删除数据记录用delete就行了，但是如何要删除复杂的外键就不是一条delete删除来实例的，我们本文章先讲一下delete删除，然后再告诉你利用触发器删除多条记录多个表。删除数据库中的数据sql 删除语句一般简单的删除数据记录用delete就行了，但是如何要删除复杂的外键就不是一条delete删除来实例的，我们本文章先讲一下delete删除，然后再告诉你利用触发器删除多...
用一条SQL语句查询出每门课都大于80分的学生姓名
两道sql题：1、用一条SQL语句查询出每门课都大于80分的学生姓名。2、删除除了自动编号不同，其他都相同的学生冗余信息。第一题数据如下：分析：每门课都大于80分，就是说学生最低分数要大于80分,我们直观看只有"王五"这个学生每门课分数都在80分以上。解决思路：这里需要用到聚合函数min(score)，查询出最低分数大于80分的学生即可。使用了聚合函数，条件就需要使用having语句。第一步，查询...
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章