实际的需求是有设备a,b,c这三台设备的维护时间分别为1个月/次,3个月/次.9个月/次,想向b表输出该设备的名称和相关项目.比如2007或2008年的,以后还可能继续想a表中添加设备所以shedingshijian值不是确定的。
现在a表内容如下:
kaiyongshijian shedingshijian ID beizhu
2007-4-3 12:00:00 12 1
2007-4-3 12:00:00 15 5
2007-4-3 12:00:00 20 9
2007-5-3 12:00:00 10 2
2007-5-3 12:00:00 7 3
2007-5-3 12:00:00 5 6
~~~~~~~~~~~~~~~~~~~~~~~~等等
表B
zhengdianbaoshi ID beizhu
2008-4-3 12:00:00 1
2008-7-3 12:00:00 5
2008-12-3 12:00:00 9
2008-3-3 12:00:00 2
2008-6-3 12:00:00 3
2008-3-3 12:00:00 6
2008-8-3 12:00:00 6~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~等等
现在a表内容如下:
kaiyongshijian shedingshijian ID beizhu
2007-4-3 12:00:00 12 1
2007-4-3 12:00:00 15 5
2007-4-3 12:00:00 20 9
2007-5-3 12:00:00 10 2
2007-5-3 12:00:00 7 3
2007-5-3 12:00:00 5 6
~~~~~~~~~~~~~~~~~~~~~~~~等等
表B
zhengdianbaoshi ID beizhu
2008-4-3 12:00:00 1
2008-7-3 12:00:00 5
2008-12-3 12:00:00 9
2008-3-3 12:00:00 2
2008-6-3 12:00:00 3
2008-3-3 12:00:00 6
2008-8-3 12:00:00 6~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~等等
shedingshijian列是需要多少时间
还有哪里不明白?
insert into 表B(zhengdianbaoshi,ID,beizhu)
select dateadd(month,shedingshijian,kaiyongshijian),ID,beizhu
from 表A
playwarcraft(时间就像乳沟,挤挤还是有的) xiaoku(野蛮人(^v^)) 你们俩三角的时候我就老看见你们,别拿我开玩笑
做个啦检修软件就差这个设备维护提醒功能啦.大家帮帮忙吧
playwarcraft(时间就像乳沟,挤挤还是有的) xiaoku(野蛮人(^v^)) 你们俩三角的时候我就老看见你们,别拿我开玩笑
--------------------
古董级的
kaiyongshijian是什麼意思? shedingshijian又是什麼意思? 還有id是指設備ID?
所謂"到时间应该维修的设备" ,又該如何判斷?
insert into b(zhengdianbaoshi ,ID,beizhu)
select dateadd(month,shedingshijian, kaiyongshijian ),ID,beizhu
from a
例如表a的ID 为1的设备,他每次的维护时间是shedingshijian 的值为12个月,它的开用时间是kaiyongshijian列的2007-4-3 12:00:00
需要计算出来2007或者2008年或者以后任意年份需要维护的时间并写到b表
把ID 为1的设备需要维护的时间写到zhengdianbaoshi 列
insert into a
select '2007-4-3 12:00:00',12,1,'' union all
select '2007-4-3 12:00:00',15,5,'' union all
select '2007-4-3 12:00:00',20,9,'' union all
select '2007-5-3 12:00:00',10,2,'' union all
select '2007-5-3 12:00:00',7,3,'' union all
select '2007-5-3 12:00:00',5,6,'' create table b (zhengdianbaoshi datetime,ID int,beizhu varchar(100))/*SQL2000*/
declare @n int, @year int
set @year =2008 --設置年份
set @n=0
while @@rowcount>0
begin
set @n=@n+1
insert into b
select dateadd(month,shedingshijian*@n, kaiyongshijian ),ID,beizhu
from a
where year(dateadd(month,shedingshijian*@n, kaiyongshijian) )=@year
endselect * from b
/*
zhengdianbaoshi ID beizhu
-------------------------------------------------------------------------
2008-04-03 12:00:00.000 1
2008-07-03 12:00:00.000 5
2008-12-03 12:00:00.000 9
2008-03-03 12:00:00.000 2
2008-07-03 12:00:00.000 3
2008-03-03 12:00:00.000 6
2008-08-03 12:00:00.000 6 */drop table a,b
hrb2008() 怎么没加我:(
insert a(kaiyongshijian,shedingshijian,ID) select '2007-5-3 12:00:00',5,16
insert a(kaiyongshijian,shedingshijian,ID) select '2007-5-3 12:00:00',2,17
挺笨的方法,哈哈