--日期转换参数
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12select CONVERT(varchar(12) , getdate(), 112 )
20040912select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12select CONVERT(varchar(12) , getdate(), 112 )
20040912select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
declare @t table(预计加工日期 varchar(10), 优先级 int, [预计加工时长(小时)] int)
insert @t select '2009-03-09', 0 , 3
union all select '2009-03-09', 1 , 4
union all select '2009-03-09', 2 , 3
union all select '2009-03-10', 1 , 2
union all select '2009-03-10', 2 , 3 select 预计加工日期,优先级,[预计加工时长(小时)],
预计加工时间=
ltrim(8+(select sum([预计加工时长(小时)]) from @t where 预计加工日期=a.预计加工日期 and 优先级<=a.优先级)-[预计加工时长(小时)])+':00-'+
ltrim(8+(select sum([预计加工时长(小时)]) from @t where 预计加工日期=a.预计加工日期 and 优先级<=a.优先级))+':00'
from @t a预计加工日期 优先级 预计加工时长(小时) 预计加工时间
---------- ----------- ----------- -------------------------------
2009-03-09 0 3 8:00-11:00
2009-03-09 1 4 11:00-15:00
2009-03-09 2 3 15:00-18:00
2009-03-10 1 2 8:00-10:00
2009-03-10 2 3 10:00-13:00(所影响的行数为 5 行)
select
A.Sid, B.pinMing, B.GangHao, C.pinMing, C.GangChang
from 表A
join 表B on a.yinYongID = B.Sid
join 表C on a.yinYongID = C.Sid
where B.pinMing like '%内容%'
and B.GangHao like '%内容%' --根据需要and也可以用or
and C.GangChang like '%内容%'--根据需要and也可以用or
insert into tb values('2009-03-09' , 0 , 3)
insert into tb values('2009-03-09' , 1 , 4)
insert into tb values('2009-03-09' , 2 , 3)
insert into tb values('2009-03-10' , 1 , 2)
insert into tb values('2009-03-10' , 2 , 3)
goselect * ,
yjjgsj = cast(8 + (select sum(sj) from tb where dt = t.dt and px <= t.px) - sj as varchar) + ':00' + '-' +
cast(8 + (select sum(sj) from tb where dt = t.dt and px <= t.px) as varchar) + ':00'
from tb tdrop table tb/*
dt px sj yjjgsj
------------------------------------------------------ ----------- ----------- -------------
2009-03-09 00:00:00.000 0 3 8:00-11:00
2009-03-09 00:00:00.000 1 4 11:00-15:00
2009-03-09 00:00:00.000 2 3 15:00-18:00
2009-03-10 00:00:00.000 1 2 8:00-10:00
2009-03-10 00:00:00.000 2 3 10:00-13:00(所影响的行数为 5 行)*/
(不知道你要的是哪个?)
select A.Sid,
B.pinMing,
B.GangHao,
C.pinMing,
C.GangChang
from a , b , c
where A.yinYongID = B.Sid and A.yinYongID = C.Sid select A.Sid,
B_pinMing = (select pinMing from b where sid = a.yinYongID),
B_GangHao = (select GangHao from b where sid = a.yinYongID),
C_pinMing = (select pinMing from c where sid = a.yinYongID),
C_GangChang = (select GangChang from c where sid = a.yinYongID)
from a
insert @t select '2009-03-09', 0 , 3
union all select '2009-03-09', 1 , 4
union all select '2009-03-09', 1 , 5
union all select '2009-03-09', 2 , 3
union all select '2009-03-10', 1 , 2
union all select '2009-03-10', 2 , 3
--解决优先级相同的问题,增加临时表处理
select *,fid=identity(int,1,1) into # from @t
select 预计加工日期,优先级,[预计加工时长(小时)],
预计加工时间=
ltrim(8+(select sum([预计加工时长(小时)]) from # where 预计加工日期=a.预计加工日期 and fid<=a.fid)-[预计加工时长(小时)])+':00-'+
ltrim(8+(select sum([预计加工时长(小时)]) from # where 预计加工日期=a.预计加工日期 and fid<=a.fid))+':00'
from # a
drop table #
/*
预计加工日期 优先级 预计加工时长(小时) 预计加工时间
---------- ----------- ----------- -------------------------------
2009-03-09 0 3 8:00-11:00
2009-03-09 1 4 11:00-15:00
2009-03-09 1 5 15:00-20:00
2009-03-09 2 3 20:00-23:00
2009-03-10 1 2 8:00-10:00
2009-03-10 2 3 10:00-13:00(6 行受影响)
*/