sql 循环语句？

llh6795 2010-09-09 02:00:41
select '2010-09-01',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-01 06:00:00.000 ' and '2010-09-01 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-02',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-02 06:00:00.000 ' and '2010-09-02 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-03',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-03 06:00:00.000 ' and '2010-09-03 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-04',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-04 06:00:00.000 ' and '2010-09-04 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-05',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-05 06:00:00.000 ' and '2010-09-05 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-06',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-06 06:00:00.000 ' and '2010-09-06 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-07',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-07 06:00:00.000 ' and '2010-09-07 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-08',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-08 06:00:00.000 ' and '2010-09-08 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-09',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-09 06:00:00.000 ' and '2010-09-09 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
这样写很麻烦，我想写个循环，上面的只是列了几天的，我要做的是全月的，我写了个循环，就是想把'2010-09-04'和'2010-09-04 06:00:00.000 '这两个日期循环，不用全列出来，后面的'2010-09-04 06:00:00.000 '也是只循环日期部分，因为后面的时间都是一样的，下面是我写的循环请高手指点？


use SMI_KQSJ
go
if object_id('SMI_KQSJ..TJB') is not null drop procedure TJB
go
create procedure TJB
@year nvarchar(4),
@month nvarchar(2)
as
declare @sqlstr nvarchar(4000)
set @sqlstr='if object_id(''smi_kqsj..bctj'') is not null drop table bctj
create table bctj(kqsj datetime,employeeid int,zaob numeric(6,2),zhongb int,yeb int)
declare @minid int,@maxid int,@date1 datetime,@month1 nvarchar(2),@date2 datetime
select @minid=min(employeeid) ,@maxid=max(employeeid) from ryxx'+@year+@month+'
select @date1=min(convert(varchar(10) ,datetimebrush,120)) from lsb'+@year+@month+'
select @month1=month(@date1)
while @maxid>=@minid
begin
set @date2=@date1
while month(@date2)=@month1
begin
insert into bctj(kqsj,zaob,employeeid)
select @date2,count(*) as zaob,@minid from lsb'+@year+@month+' where datetimebrush between @date2+'' 06:00:00.000 '' and @date2+'' 18:30:00.000'' and worktypename=''倒班'' group by employeeid having count(*)>=2
print(''''+@date2+'' 06:00:00.000 '')
set @date2=dateAdd(day,1,@date2)
end

set @minid=@minid+1
end


'
exec(@sqlstr)
--print(@sqlstr)

go
exec TJB '2010','09'
...全文
185 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
llh6795 2010-09-10
  • 打赏
  • 举报
 回复
楼上的好厉害，要是把日期格式改成120的怎么就不行了啊
lg200015017 2010-09-10
  • 打赏
  • 举报
 回复 


select id,convert(varchar(30),dateadd("hh",-3,t1),111) rq,count(*) from test a

where convert(varchar(30),dateadd("hh",-3,t1),108) between '11:30:00' and '23:59:59'

and not exists (select 1 from test where id = a.id and  convert(varchar(30),t1,111) = convert(varchar(30),dateadd("d",1,a.t1),111) and convert(varchar(30),t1,108) between '03:00:00' and '14:30:00')

group by id,convert(varchar(30),dateadd("hh",-3,t1),111)

having count(*)>=2

order by rq
lg200015017 2010-09-10
  • 打赏
  • 举报
 回复 


select getdate() --当前时间

select convert(varchar(30),getdate(),111) --只显示日期

select convert(varchar(30),getdate(),108) --只显示时间

select convert(varchar(30),getdate(),120) --完整的

select dateadd("hh",-3,getdate()) -- 当前时间往前移3小时 同样可以改一下数,往前移10个小时

select dateadd("d",1,getdate()) --当前时间的后一天

这几个函数可在运行着看一下结果，就能明白了
这个：
当天'23:00:00' 到次日 '10:00:00' 都算作是前一天的夜班，怎么统计啊？
convert 运算出来的结果是字符串，如果直接比较，就存在23，24 ，1 - 10 这些开头的字串，不好比较而且第二天1-10点的工作量是算在前一天的，直接比较1-10点的就算在当天了。 那么我们把这个时间往前移10个小时呢？日期对了，时间也卡在了13：00：00 - 23：59：59 了，这样就好弄了吧。
可以参考那个14：30-第二天03：00的例子。
lg200015017 2010-09-10
  • 打赏
  • 举报
 回复 


select id,convert(varchar(30),dateadd("hh",-3,t1),111) rq,count(*) from test a

where convert(varchar(30),dateadd("hh",-3,t1),108) between '11:30:00' and '23:59:59'

and not exists (select 1 from test where convert(varchar(30),t1,111) = convert(varchar(30),dateadd("d",1,a.t1),111) and convert(varchar(30),t1,108) between '03:00:00' and '14:30:00')

group by id,convert(varchar(30),dateadd("hh",-3,t1),111)

having count(*)>=2

order by rq

lg200015017 2010-09-10
  • 打赏
  • 举报
 回复
如果0点至3点的工作量算是前一天的那么：


select id,convert(varchar(30),dateadd("hh",-3,t1),111) rq,count(*) from test 

where convert(varchar(30),dateadd("hh",-3,t1),108) between '11:30:00' and '23:59:59'

group by id,convert(varchar(30),dateadd("hh",-3,t1),111)

having count(*)>=2

order by rq
llh6795 2010-09-10
  • 打赏
  • 举报
 回复
select employeeid,convert(varchar(30),dateadd("hh",-3,datetimebrush),111) kqsj,min(datetimebrush) as zhongbs,max(datetimebrush) as zhongbx,'中班' as bcbs,'1' as sbts from lsb201009
where convert(varchar(30),dateadd("hh",-3,datetimebrush),108) between '11:30:00' and '23:59:59' and worktypename='倒班'
group by employeeid,convert(varchar(30),dateadd("hh",-3,datetimebrush),111)
having count(*)>=2
order by kqsj
这个已经可以实现了，我想再加一个条件，在次日的03:00---14:30 无记录的？
llh6795 2010-09-10
  • 打赏
  • 举报
 回复
当天'23:00:00' 到次日 '10:00:00' 都算作是前一天的夜班，怎么统计啊？
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
between '2010-09-01 14:30:00.000 ' and '2010-09-02 03:00:00.000' 楼上的好聪明，现在对了，还有个时间区间，楼上的帮我看看，跟上面的数据都一样，就是条件时间变了，这个关系到两天？
lg200015017 2010-09-09
  • 打赏
  • 举报
 回复 


select id,convert(varchar(30),t1,111) rq,count(*) from test 

where convert(varchar(30),t1,108) between '06:00:00' and '18:30:00'

group by id,convert(varchar(30),t1,111)

having count(*)>=2

order by rq
duanzhi1984 2010-09-09
  • 打赏
  • 举报
 回复
把ID加上。

create table #t(da datetime,id varchar(10) )

insert into #t

select '2010-09-02 8:10:42',2062 union all   

select '2010-09-04 7:21:18',2062union all   

select '2010-09-04 16:41:25',2062union all   

select '2010-09-05 7:08:13',2062union all   

select '2010-09-05 16:40:17',2062union all   

select '2010-09-06 15:31:07',2062union all   

select '2010-09-07 1:02:29',2062union all   

select '2010-09-07 1:02:31',2062union all   

select '2010-09-07 15:30:29',2062union all   

select '2010-09-08 1:03:18',2062union all   

select '2010-09-09 0:14:44',2062union all   

select '2010-09-09 8:13:34',2062union all   

select '2010-09-09 8:13:36',2062



 









select id,convert(varchar(10),ta ,126)date ,count(*)co from #t join 

(select dateadd(d,number,'2010-09-01') ta from master..spt_values   where type='P' AND number<30)as t

on convert(varchar(10),ta,126)= convert(varchar(10),da,126) where 

da between convert(varchar(10),da,126)+' 06:00:00.000' and convert(varchar(10),da,126)+' 18:30:00.000'

group by ta,id having count(*)>=2 





id         date       co

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

2062       2010-09-04 2

2062       2010-09-05 2

2062       2010-09-09 2



(3 行受影响)
duanzhi1984 2010-09-09
  • 打赏
  • 举报
 回复 
create table #t(da datetime,id varchar(10) )

insert into #t

select '2010-09-02 8:10:42'union all   

select '2010-09-04 7:21:18'union all   

select '2010-09-04 16:41:25'union all   

select '2010-09-05 7:08:13'union all   

select '2010-09-05 16:40:17'union all   

select '2010-09-06 15:31:07'union all   

select '2010-09-07 1:02:29'union all   

select '2010-09-07 1:02:31'union all   

select '2010-09-07 15:30:29'union all   

select '2010-09-08 1:03:18'union all   

select '2010-09-09 0:14:44'union all   

select '2010-09-09 8:13:34'union all   

select '2010-09-09 8:13:36'













select convert(varchar(10),ta ,126),count(*) from #t join 

(select dateadd(d,number,'2010-09-01') ta from master..spt_values   where type='P' AND number<30)as t

on convert(varchar(10),ta,126)= convert(varchar(10),da,126) where 

da between convert(varchar(10),da,126)+' 06:00:00.000' and convert(varchar(10),da,126)+' 18:30:00.000'

group by ta having count(*)>=2 



2010-09-04	2

2010-09-05	2

2010-09-09	2


试试
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
国哥明白了吗？
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
insert into bctj(kqsj,zaob,employeeid)
select '2010-09-01',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-01 06:00:00.000 ' and '2010-09-01 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-02',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-02 06:00:00.000 ' and '2010-09-02 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-03',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-03 06:00:00.000 ' and '2010-09-03 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-04',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-04 06:00:00.000 ' and '2010-09-04 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-05',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-05 06:00:00.000 ' and '2010-09-05 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
union all
select '2010-09-06',count(*) as zaob,employeeid from lsb201009 where datetimebrush between '2010-09-06 06:00:00.000 ' and '2010-09-06 18:30:00.000' and worktypename='倒班' group by employeeid having count(*)>=2
这是六天的，一个月三十天还是三十一天，也不好写啊，要是这样写就要写三是或者三十一句，所以我想写个循环，把里面的日期循环，不用写这么多条句子
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
刷卡时间 ID
2010-09-02 8:10:42 2062
2010-09-04 7:21:18 2062
2010-09-04 16:41:25 2062
2010-09-05 7:08:13 2062
2010-09-05 16:40:17 2062
2010-09-06 15:31:07 2062
2010-09-07 1:02:29 2062
2010-09-07 1:02:31 2062
2010-09-07 15:30:29 2062
2010-09-08 1:03:18 2062
2010-09-09 0:14:44 2062
2010-09-09 8:13:34 2062
2010-09-09 8:13:36 2062
上面这是原始的表（名lsh201009）结构和数据 我想实现
2062 2010-09-04 2
就是在 2010-09-04号 '2010-09-04 06:00:00.000 ' and '2010-09-04 18:30:00.000' 之间》=2条以上的记录数，这个只是列了其中的一条，我要做全月的

hao1hao2hao3 2010-09-09
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 llh6795 的回复:]
没有达到我想要的效果
[/Quote]

晕！话怎么都只说一半啊！这么长要很有耐心才能看的，
这样吧！
你把表结构列出来，还有你想要实现的效果也列出来。
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
2010-09-02 00:00:00.000 268 3
2010-09-03 00:00:00.000 268 2
2010-09-06 00:00:00.000 268 2
2010-09-02 00:00:00.000 269 2
2010-09-03 00:00:00.000 269 2
2010-09-04 00:00:00.000 270 2
2010-09-05 00:00:00.000 270 2
2010-09-06 00:00:00.000 271 2
2010-09-07 00:00:00.000 271 2
2010-09-01 00:00:00.000 272 2
2010-09-08 00:00:00.000 272 4
2010-09-08 00:00:00.000 273 2
2010-09-02 00:00:00.000 273 3
2010-09-03 00:00:00.000 273 2
2010-09-01 00:00:00.000 274 3
2010-09-06 00:00:00.000 274 3
2010-09-07 00:00:00.000 274 2
上面这是我要的结果
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
43 2010-09-01 00:00:00.000
这个是循环出来的结果，循环出来天数全部重复出现了
llh6795 2010-09-09
  • 打赏
  • 举报
 回复
没有达到我想要的效果
ws_hgo 2010-09-09
  • 打赏
  • 举报
 回复
这么长........
hao1hao2hao3 2010-09-09
  • 打赏
  • 举报
 回复
循环出了问题吗？

还是没有达到你想要的效果？
SQL 循环语句几种写法
【代码】SQL 循环语句几种写法。
oracle使用sql循环语句,常用SQL/oracle循环语句
2、使用T-SQL标准控制结构： 1 定义语句块 2 IF ... ELSE语句 3 IF EXISTS语句 语法： declare select @lname = ‘Smith’ if exists(select * from titles where au_lname = @lname) begin select @msg = ‘There ...
SQL循环语句
有时我们需要添加很多测试的数据 在不使用Java的情况下 如何用SQL语句进行添加 ? 本文将讲述单层循环，双层循环，以及日期循环 一、单层循环 其中涉及到循环的使用。我们欲通过这个存储过程，来达到往表中插入数据的...
[MyBatis]--(4)动态SQL语句--(3)循环
2、SQL语句的集合片段 集合片段在SQL语句中有两种存在形式，一种是位于in子句，用于枚举所有情况；另一种则是位于value子句，用于数据的批量操作，如多条数据添加、多条数据修改等。foreach...
SQL Server中的循环语句示例
在本文中，我们介绍了SQL Server中的两种循环语句：WHILE循环和CURSOR循环。WHILE循环适用于根据条件重复执行一组语句，而CURSOR循环适用于在数据库表中遍历行并执行操作。在SQL Server中，循环语句是一种强大的工具...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章