子陌哥哥你要是在就帮帮我吧.求一个触发器,能完整解决送600分啦.

Question

表 a
 
DateAndTime                    TagIndex              Val
2006-10-6 15:51:00                 1                0
2006-10-6 15:51:00                 2                0
2006-10-6 15:51:00                 3                0
2006-10-6 15:51:00                 4                111
2006-10-6 15:59:00                 3                1
2006-10-6 15:59:25                 4                111.1
2006-10-6 16:15:25                 4                122.5
2006-10-6 16:20:00                 3                0
2006-10-6 16:59:00                 1                1
2006-10-6 16:59:00                 4                122.6
2006-10-6 16:59:00                 4                133.1
2006-10-6 16:59:00                 1                0以上TagIndex列1.2.3是设备号,4是流量表
求一个流量的触发器
设备1.2.3永远不会同时启动.数据库重新启动的时候需要初始化状态,所以TagIndex列1.2.3的val列=0
只能做计算条件不能做为触发条件
最好用下一个设备状态比如:TagIndex列1 Val列是1做为触发条件来求上一个设备比如TagIndex列3 Val列是1所用的流量
最后形成的表b
DateAndTime                    TagIndex        Val
2006-10-6 16:20:00                 3           10.6
2006-10-6 16:59:00                 1           11.5

Answer 1

对不起啊,我给简化的太多啦
表 a
 
DateAndTime                    TagIndex              Val
2006-10-6 15:51:00                 1                0
2006-10-6 15:51:00                 2                0
2006-10-6 15:51:00                 3                0
2006-10-6 15:51:00                 4                111
2006-10-6 15:59:00                 3                1
2006-10-6 15:59:25                 4                111.1
中间有很多条数据一秒记录一次都是4的数据
2006-10-6 16:15:25                 4                122.5
2006-10-6 16:20:00                 3                0
2006-10-6 16:59:00                 1                1
2006-10-6 16:59:00                 4                122.6
中间有很多条数据一秒记录一次都是4的数据
2006-10-6 16:59:00                 4                133.1
2006-10-6 16:59:00                 1                0

Answer 2

子陌哥哥???哈哈,太暧昧了!!!估计被你吓着了!!!周末去买衣服的时候有个PLMM服务员一直叫我哥哥:哥哥买这个外套吧,很适合你的,很超值!
                                             哥哥这个裤子现在打折才99,还送一条裤带呢!
                                             平时都没有的!
                                             要不您再看看这个适合您不?
                                             一直哥哥,我多看了几眼!怎么看都是美女!
                                             呵呵,但是```````

Answer 3

create table a(DateAndTime datetime,TagIndex int,Val numeric(5,1))
create table b(DateAndTime datetime,TagIndex int,Val numeric(5,1))
gocreate trigger trg_a on a
for insert 
as
begin
    if exists(select 
                   1 
               from 
                   inserted m,a n 
               where 
                   m.TagIndex=n.TagIndex and m.DateAndTime>n.DateAndTime
                   and 
                   m.TagIndex in(1,2,3) and m.Val=0 and n.Val=1
                   and
                   not exists(select
                                  1 
                              from 
                                  a 
                              where 
                                  TagIndex=m.TagIndex and DateAndTime>=n.DateAndTime 
                                  and 
                                  DateAndTime<m.DateAndTime and Val=0)
                   )
    begin
        insert into b(DateAndTime,TagIndex,Val)
        select
            t.DateAndTime,
            t.TagIndex,
            (select max(Val) from a)-
            (select 
                 top 1 Val 
             from 
                 a p 
             where 
                 DateAndTime<=(select 
                                   top 1 DateAndTime 
                               from 
                                   a 
                               where 
                                   TagIndex=t.TagIndex and val=1 
                               order by 
                                   DateAndTime desc) 
             order by 
                 Val desc)
        from
            inserted t
    end
end
goinsert into a select '2006-10-6 15:51:00',1,0
insert into a select '2006-10-6 15:51:00',2,0
insert into a select '2006-10-6 15:51:00',3,0
insert into a select '2006-10-6 15:51:00',4,111
insert into a select '2006-10-6 15:59:00',3,1
insert into a select '2006-10-6 15:59:25',4,111.1
insert into a select '2006-10-6 15:59:29',4,111.1
insert into a select '2006-10-6 16:00:29',4,112.0
insert into a select '2006-10-6 16:15:25',4,122.5
insert into a select '2006-10-6 16:20:00',4,122.6
insert into a select '2006-10-6 16:20:00',3,0
insert into a select '2006-10-6 16:58:00',1,1
insert into a select '2006-10-6 16:59:00',4,122.6
insert into a select '2006-10-6 16:59:00',4,122.9
insert into a select '2006-10-6 16:59:00',4,133.1
insert into a select '2006-10-6 16:59:00',1,0
select * from b
/*
DateAndTime                 TagIndex    Val     
--------------------------- ----------- ------- 
2006-10-06 16:20:00.000     3           11.6
2006-10-06 16:59:00.000     1           10.5
*/
godrop trigger trg_a
drop table a,b
go

Answer 4

思路如下：如果当前触发的insert事件所insert到表中的数据为1/2/3某个设备的关闭(0)操作，而且表中该设备的前一个动作是打开(1)  --if exists()中判断的逻辑那么执行b表的insert操作，流量值取当前距离关闭时刻最近的前一个时刻的流量作为截至流量，取当前设备最接近且早于当前关闭(0)操作的开启(1)操作时刻的流量作为起始流量，二者的差为该设备最近一次开启到关闭操作过程中的流量。

Answer 5

恩,我写的有问题,应该有时间差的DateAndTime                    TagIndex              Val
2006-10-6 15:51:00                 1                0
2006-10-6 15:51:00                 2                0
2006-10-6 15:51:00                 3                0
2006-10-6 15:51:00                 4                111
2006-10-6 15:59:00                 3                1
2006-10-6 15:59:25                 4                111.1
中间有很多条数据一秒记录一次都是4的数据
2006-10-6 16:15:25                 4                122.5
2006-10-6 16:20:00                 3                0
2006-10-6 16:59:00                 1                1
2006-10-6 17:05:38                 4                122.6
中间有很多条数据一秒记录一次都是4的数据
2006-10-6 17:12:01                4                133.1
2006-10-6 17:16:22                1                0子陌哥哥,我把你写的这段放到:管理触发器,它就能自动执行从a表到b表的自动转换吧?
还有个问题就是实际的TagIndex编号一共17个其中16个设备号一个流量计,触发器都能自动完成从a表到b表的自动转换吧?
create table a(DateAndTime datetime,TagIndex int,Val numeric(5,1))
create table b(DateAndTime datetime,TagIndex int,Val numeric(5,1))
gocreate trigger trg_a on a
for insert 
as
begin
    if exists(select 
                   1 
               from 
                   inserted m,a n 
               where 
                   m.TagIndex=n.TagIndex and m.DateAndTime>n.DateAndTime
                   and 
                   m.TagIndex in(1,2,3) and m.Val=0 and n.Val=1
                   and
                   not exists(select
                                  1 
                              from 
                                  a 
                              where 
                                  TagIndex=m.TagIndex and DateAndTime>=n.DateAndTime 
                                  and 
                                  DateAndTime<m.DateAndTime and Val=0)
                   )
    begin
        insert into b(DateAndTime,TagIndex,Val)
        select
            t.DateAndTime,
            t.TagIndex,
            (select max(Val) from a)-
            (select 
                 top 1 Val 
             from 
                 a p 
             where 
                 DateAndTime<=(select 
                                   top 1 DateAndTime 
                               from 
                                   a 
                               where 
                                   TagIndex=t.TagIndex and val=1 
                               order by 
                                   DateAndTime desc) 
             order by 
                 Val desc)
        from
            inserted t
    end
end
go
就是这段都写进去就可以吧?还是从if开始写呀?哪~~哥哥帮我写一条我就能很安稳的过半年的好日子,不叫哥哥叫什么!!!
不过下次就该做库房数据库哪,嘎嘎,继续磨蹭1年做出来.

Answer 6

create trigger trg_a on a
for insert 
as
begin
    if exists(select 
                   1 
               from 
                   inserted m,a n 
               where 
                 --限定只有设备1/2/3的关闭操作才能触发以下操作
                   m.TagIndex in(1,2,3)
                   and 
                   m.Val=0
                   and 
                   --限定当前关闭操作之前必须存在同一设备的开启事件
                   m.TagIndex=n.TagIndex
                   and 
                   m.DateAndTime>n.DateAndTime
                   and 
                   n.Val=1
                   and
                   --限定同一设备关闭时间之前的最新操作为开启操作
                   not exists(select
                                  1 
                              from 
                                  a 
                              where 
                                  TagIndex=m.TagIndex and DateAndTime>=n.DateAndTime 
                                  and 
                                  DateAndTime<m.DateAndTime and Val=0)
                   )
    begin
        insert into b(DateAndTime,TagIndex,Val)
        select
            t.DateAndTime,
            t.TagIndex,
            --取得当前时刻的流量计数；原则上流量最大值即当前流量
            (select max(Val) from a)-
            --取得最近一次开启设备时刻的流量值
            (select
                 top 1 Val 
             from 
                 a p 
             where 
              --取得最近一次开启设备的时刻，原则上小于等于这个时刻的流量值为本次计量的起始值
                 DateAndTime<=(select
                                   top 1 DateAndTime 
                               from 
                                   a 
                               where 
                                   TagIndex=t.TagIndex and val=1 
                               order by 
                                   DateAndTime desc) 
             order by 
                 Val desc)
        from
            inserted t
    end
end
go

Answer 7

create trigger trg_a on a
for insert 
as
begin
    if exists(select 
                   1 
               from 
                   inserted m,a n 
               where 
                   m.TagIndex in(1,2,3) --把所有的设备号列表在这里列出，除了流量
                   and 
                   m.Val=0
                   and 
                   m.TagIndex=n.TagIndex
                   and 
                   m.DateAndTime>n.DateAndTime
                   and 
                   n.Val=1
                   and
                   not exists(select
                                  1 
                              from 
                                  a 
                              where 
                                  TagIndex=m.TagIndex and DateAndTime>=n.DateAndTime 
                                  and 
                                  DateAndTime<m.DateAndTime and Val=0)
                   )
    begin
        insert into b(DateAndTime,TagIndex,Val)
        select
            t.DateAndTime,
            t.TagIndex,
            (select max(Val) from a)-
            (select
                 top 1 Val 
             from 
                 a p 
                 DateAndTime<=(select
                                   top 1 DateAndTime 
                               from 
                                   a 
                               where 
                                   TagIndex=t.TagIndex and val=1 
                               order by 
                                   DateAndTime desc) 
             order by 
                 Val desc)
        from
            inserted t
    end
end
go

Answer 8

它就能自动执行从a表到b表的自动转换吧?
----------------------------------------------------------------------------
是的
还有个问题就是实际的TagIndex编号一共17个其中16个设备号一个流量计,触发器都能自动完成从a表到b表的自动转换吧?
----------------------------------------------------------------------------
在我上一贴里将m.TagIndex in(1,2,3)修改为m.TagIndex <> 17 --假定17为流量，1-16为设备编号