34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb
(
JO001 nvarchar(50),
JO002 nvarchar(50),
JO005 nvarchar(50),
JO011 nvarchar(50),
JO012 nvarchar(50),
JO013 nvarchar(50),
JO014 nvarchar(50),
JO042 nvarchar(50)
)
insert into tb
select'PZC141:1410','PZC141','GC管板NC切割','2012/10/11 09:00:00','2012/10/11 16:00:00','2012/10/11 09:00:00','2012/10/11 16:00:00',NULL
union
select'PZC141:1420','PZC141','GC管板组装','2012/10/14 12:00:00','2012/10/17 15:00:00','2012/10/14 12:00:00','2012/10/17 15:00:00','PZC141:1410'
union
select'PZC141:1430','PZC141','GC管板打定位孔','2012/10/20 16:00:00','2012/10/20 19:00:00','2012/10/20 16:00:00','2012/10/20 19:00:00','PZC141:1420'
union
select'PZC141:1440','PZC141','GC管板加工','2012/10/21 10:00:00','2012/10/22 00:00:00','2012/10/21 10:00:00','2012/10/22 00:00:00','PZC141:1430'
union
select'PZC141:1445','PZC141','GC管板打定位孔','2012/10/20 19:00:00','2012/10/20 21:00:00','2012/10/20 19:00:00','2012/10/20 21:00:00','PZC141:1420'
union
select'PZC141:1446','PZC141','GC管板加工','2012/10/21 09:00:00','2012/10/22 00:00:00','2012/10/21 09:00:00','2012/10/22 00:00:00','PZC141:1445'
union
select'PZC141:1450','PZC141','GC管板脱脂','2012/10/23 09:00:00','2012/10/23 10:00:00','2012/10/23 09:00:00','2012/10/23 10:00:00','PZC141:1440;PZC141:1446'
union
select'QZC439:1175','QZC439','AE胀管','2012/12/10 09:00:00','2012/12/11 08:20:00','2012/12/10 09:00:00','2012/12/11 08:20:00',NULL
union
select'QZC439:1660','QZC439','GC胀管','2012/12/01 15:00:00','2012/12/02 10:00:00','2012/12/01 15:00:00','2012/12/02 10:00:00','QZC439:1650'
union
select'QZC439:1960','QZC439','GH胀管','2012/12/11 08:00:00','2012/12/11 08:53:00','2012/12/11 08:00:00','2012/12/11 08:19:00','QZC439:1910'
union
select'QZC439:2000','QZC439','全体配管','2012/12/12 08:28:00','2012/12/20 11:27:32','2012/12/12 08:28:00',NULL,'QZC439:1175;QZC439:1660;QZC439:1960'
union
select'QZC439:2030','QZC439','整机气密','2012/12/20 13:27:32','2012/12/20 13:27:33',NULL,NULL,'QZC439:2000'
select
JO001,
JO002,
JO005,
convert(varchar(19),(select MAX(JO011) from APSJOB b where a.JO042 like '%'+ b.JO001+'%'),120)
from
APSJOB a
也不快
select
JO001,
JO002,
JO005,
convert(varchar(19),(select top 1 JO011 from tb b where b.JO042 like '%'+a.JO001+'%' order by JO011 desc),120)
+'-'+convert(varchar(19),(select top 1 JO012 from tb c where c.JO042 like '%'+a.JO001+'%' order by JO012 desc),120)
--,
--convert(varchar(19),(select top 1 JO013 from tb d where a.JO001=d.JO042 order by JO013 desc),120)
--+'-'+convert(varchar(19),(select top 1 JO014 from tb e where a.JO001=e.JO042 order by JO014 desc),120)
from
tb a
我这样改了下 执行速度好慢
select
JO001,
JO002,
JO005,
convert(varchar(19),(select top 1 JO011 from tb b where a.JO001=b.JO042 order by JO011 desc),120)
+'-'+convert(varchar(19),(select top 1 JO012 from tb c where a.JO001=c.JO042 order by JO012 desc),120),
convert(varchar(19),(select top 1 JO013 from tb d where a.JO001=d.JO042 order by JO013 desc),120)
+'-'+convert(varchar(19),(select top 1 JO014 from tb e where a.JO001=e.JO042 order by JO014 desc),120)
from
tb a
为空的没处理