sql时间查询问题

netbor 2011-03-31 11:58:03

原数据:
Date Line KittingNo Model PerPcb dt
------------------------------------------------------------------------------------------------------
2011-3-29 1 101000007513 TCDA18 1 2011-03-29 08:50:03
2011-3-29 1 101000007513 TCDA18 1 2011-03-29 08:49:23
2011-3-29 1 101000007513 TCDA18 1 2011-03-29 08:48:45
2011-3-29 1 101000007513 TCDA18 1 2011-03-29 08:45:15
2011-3-29 2 101000007473 d2066 1 2011-03-29 08:54:11
2011-3-29 3 101000007370 JBMTE3NB 1 2011-03-29 08:47:05
2011-3-29 4 101000007475 u928 1 2011-03-29 08:49:46
2011-3-29 4 101000007475 u928 1 2011-03-29 08:49:50

希望查询的最终数据：
Date Line KittingNo Model PerPcb 8:30:00-9:30:00 9:30:00-10:30:00 …… 23:30:00-00:30:00 ……
2011-3-29 1 101000007513 TCDA18 1 0 4条记录注意这里的时间跨度
2011-3-29 2 101000007473 d2066 6 0 1
2011-3-29 3 101000007370 JBMTE3NB 1 0 1
2011-3-29 4 101000007475 u928 4 0 2

...全文

97 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

AcHerat 2011-03-31

打赏
举报



create table tb(Date datetime,Line int,KittingNo varchar(20),Model varchar(20),PerPcb int,dt datetime)

insert into tb

select '2011-3-29'	,1	,'101000007513',	'TCDA18',	 1	 ,'2011-03-29 08:50:03' union all

select '2011-3-29'	,1	,'101000007513',	'TCDA18',	 1	 ,'2011-03-29 08:49:23' union all

select '2011-3-29'	,1	,'101000007513',	'TCDA18',	 1	 ,'2011-03-29 08:48:45' union all

select '2011-3-29'	,1	,'101000007513',	'TCDA18',	 1	 ,'2011-03-29 08:45:15' union all

select '2011-3-29'	,2	,'101000007473',	'd2066',	 1	 ,'2011-03-29 08:54:11' union all

select '2011-3-29'	,3	,'101000007370',	'JBMTE3NB', 1	 ,'2011-03-29 08:47:05' union all

select '2011-3-29'	,4	,'101000007475',	'u928',	 1	 ,'2011-03-29 08:49:46' union all

select '2011-3-29'	,4	,'101000007475',	'u928',	 1	 ,'2011-03-29 08:49:50'

go



declare @sql varchar(max)



select * into #tb

from(

	select '08:30' as sta,'09:30' as eta union all

	select '09:30','10:30' union all

	select '10:30','11:30' union all

	select '11:30','12:30' union all

	select '12:30','13:30' union all

	select '13:30','14:30' union all

	select '14:30','15:30' union all

	select '15:30','16:30' union all

	select '16:30','17:30' union all

	select '17:30','18:30' union all

	select '18:30','19:30' union all

	select '19:30','20:30' union all

	select '20:30','21:30' union all

	select '21:30','22:30' union all

	select '22:30','23:30' union all

	select '23:30','00:30' union all

	select '00:30','01:30' union all

	select '01:30','02:30' union all

	select '02:30','03:30' union all

	select '03:30','04:30' union all

	select '04:30','05:30' union all

	select '05:30','06:30' union all

	select '06:30','07:30' union all

	select '07:30','08:30')t



set @sql = 'select convert(varchar(10),Date,120) as Date,Line,KittingNo,PerPcb'

select @sql = @sql + ',sum(case when dt between convert(varchar(11),dt,120) + ''' + sta 

				+ ''' and convert(varchar(11),dt,120) + ''' + eta + ''' then 1 else 0 end)[' + sta + '-' + eta + ']'

from #tb

select @sql = @sql + ' from tb group by convert(varchar(10),Date,120),Line,KittingNo,PerPcb'

exec(@sql)



drop table tb,#tb



/*



Date       Line        KittingNo            PerPcb      08:30-09:30 09:30-10:30 10:30-11:30 11:30-12:30 12:30-13:30 13:30-14:30 14:30-15:30 15:30-16:30 16:30-17:30 17:30-18:30 18:30-19:30 19:30-20:30 20:30-21:30 21:30-22:30 22:30-23:30 23:30-00:30 00:30-01:30 01:30-02:30 02:30-03:30 03:30-04:30 04:30-05:30 05:30-06:30 06:30-07:30 07:30-08:30

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

2011-03-29 1           101000007513         1           4           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0

2011-03-29 2           101000007473         1           1           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0

2011-03-29 3           101000007370         1           1           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0

2011-03-29 4           101000007475         1           2           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0



(4 行受影响)

快溜 2011-03-31

打赏
举报

参考

create table tba(id int,StartDate datetime,EndDate datetime)

insert into tba select 1,'2011-3-10','2011-3-16'

create table tbb(id int,aid int)

insert into tbb select 1,1 union select 2,1



create table tbc(id int,bid int,EffectDate datetime)

insert into tbc

select 1,1,'2011-3-10' union all

select 2,1,'2011-3-10' union all

select 3,1,'2011-3-10' union all

select 4,1,'2011-3-11' union all

select 5,2,'2011-3-11' union all

select 6,2,'2011-3-16' union all

select 7,1,'2011-3-11' union all

select 8,1,'2011-3-14' union all

select 9,2,'2011-3-11' union all

select 10,2,'2011-3-12' 





declare @str varchar(3000)

set @str=''

select @str= @str+' ,max(case when convert(varchar(10),date,120)='''+

convert(varchar(10),dateadd(dd,number,b.StartDate),120)+''' then [count] end) as ['+

convert(varchar(10),dateadd(dd,number,b.StartDate),120)+']'

from master..spt_values a,tba b

where type='p' and dateadd(dd,number,b.StartDate)<=b.EndDate

set @str='select bid'+@str+' from (select dateadd(dd,number,b.StartDate) as date,d.bid,

     [count]=(select count(1) from tbc where bid=d.bid and EffectDate=dateadd(dd,number,b.StartDate))

from master..spt_values a,tba b,tbb c,tbc d

where type=''p'' and dateadd(dd,number,b.StartDate)<=b.EndDate

      and b.id=c.aid and c.id=d.bid

group by dateadd(dd,number,b.StartDate),d.bid) a group by bid'

--print @str

exec(@str)

/*

bid         2011-03-10  2011-03-11  2011-03-12  2011-03-13  2011-03-14  2011-03-15  2011-03-16

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

1           3           2           0           0           1           0           0

2           0           2           1           0           0           0           1

警告: 聚合或其他 SET 操作消除了空值。

--小F-- 2011-03-31

打赏
举报

select

   Date,Line,KittingNo,Model,PerPcb ,

   sum(case when convert(varchar(12),dt,108) between '08:30:00' and '09:30:00' then 1 elae 0 end) as '8:30:00-9:30:00',

...

from

   tb

group by

    Date,Line,KittingNo,Model,PerPcb

Rotel-刘志东 2011-03-31

打赏
举报

select Date, Line, KittingNo, Model,PerPcb 

case dt when dt='

....

end

from tb

Rotel-刘志东 2011-03-31

打赏
举报

select Date, Line, KittingNo, Model,PerPcb 

case dt when dt='

....

 

from tb

而且它没有提供sql代码格式化的功能，咱们只能自己做手动格式化，手动格式化sql总是需要花费不少时间的。它也是一款拥有智能提示的sql编程工具，而且小巧灵活。总而言之，它也是一款能够提高sql编程效率的工具。 ...

一、时间查询语句--SQLserver1、查询日期时间1）查询日期+时间，使用SQLserver内置函数GETDATE，返回当前日期和时间；SELECT GETDATE(); --今天SELECT GETDATE()-1;--昨天SELECT DateAdd(YEAR, -1, GETDATE());--...

查询结果：2019-08-20 04:25:17 3、时间查询函数 #获取年份 SELECT YEAR(NOW()) AS 'year'; #获取月份 SELECT MONTH(NOW()) AS 'mouth'; #获取日期 SELECT DAY(NOW()) AS 'day' ; #获取当前日期 SELECT CURRENT_...

时间区间查询字段类型：time或date select * from SAMPLE s where s.CREATE_TIME >= to_date('2021-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') 字段类型：varchar select * from F_IRMS i where to_...时间查询异

Sql 查询时间段内容

疑难问题

22,209

社区成员

121,731

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

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

+ 用AI写文章