wodeqq584958184
2018-04-24 01:31
采纳率: 77.8%
浏览 1.1k

关于SQL server触发器编程问题

本人目前处于实习生,公司给了我一个项目,项目进度快接近完成了,无奈遇到了个大问题,真的不知道该怎么解决,公司又没人带,我上司也只是懂一点点,但不是专家,他是学VS编程的,看看有没有SQL 大神或专家帮我分析和解决下这问题,可能想了解的更详细可以加我QQ:584958184.

现在公司有台机器,机器的状态有0,1,2,3,机器开启运行状态的时候是0,其他的 1、2和3对应的是其他状态,机器一开启就会自动有数据生成,生成的数据信息如(图1),

图1

down机的时候,(也就是从0变为非0的时候,也就是0到1,或0到2,或者0到3这样的状态)table2会有数据记录产生,产生的数据记录其实就是错误代码。而每一次table1发生down机状态记录的时候,都会有个故障时间数据产生(即table1的非0数据记录的itime字段),故障了那么table2肯定会插入错误代码,但在这一个故障时间段直到下次机器变为0状态恢复运行时,错误代码有很多,例如下图是其中一个table1的down机过程:

图2

下图就是table2造成此次down机的所有错误代码

图3

由此可见,此次down机状态的时间是13点58分35秒,并且在13点59分22秒机器恢复运行(table2里runtime字段时间其实就是机器下次恢复运行变为0的时间),这2秒的时间里造成此次down机的错误代码有许多条,并且有些错误代码都在一个时间点上13点58分36秒,但有些不是,

现在我上司想要实现的效果就是,table1的每一次down机状态,我只要table2相对应此次down机状态的最先报错第一条代码,其他错误代码其他时间都不要,只要一条,并把此条错误代码数据记录的location字段和pointer字段还有itime值插入到table4中,效果如下图(图中效果的数据记录是手动插入):

图4

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

3条回答 默认 最新

  • Haisen大王 2018-04-24 01:51
    最佳回答
    BEGIN
             CREATE TEMPORARY TABLE IF NOT EXISTS t4(
                        flag VARCHAR(1),
                licensetype VARCHAR(2),
                carno VARCHAR(16),
                carno_original  VARCHAR(16),
                pointcodeEnt VARCHAR(12),
                pointcodeExt VARCHAR(12),
                pointnameEnt  VARCHAR(100),
                pointnameExt  VARCHAR(100),
                devicecode VARCHAR(18),
                devicecodeExt VARCHAR(18),
                cpicEnterPath  VARCHAR(200),
                cpicExitPath   VARCHAR(200),
                carcolor VARCHAR(4),
                    plate_position VARCHAR(45),
                collectiondate1 DATETIME,
                collectiondate2 DATETIME
            );
    
                    SET @flag:= '1';
            INSERT INTO t4 SELECT
                                @flag:= '2' AS flag,
                    @licensetype:=IFNULL(t2.licensetype,t3.licensetype) AS licensetype,
                    @carno:= IFNULL(t2.carno,t3.carno) AS carno,
                    @carno_original:= IFNULL(t2.carno_original,t3.carno_original) AS carno_original,
                    @pointcodeEnt:=IFNULL(t2.pointcode,'') AS pointcodeEnt,
                    @pointcodeExt:=IFNULL(t3.pointcode,'') AS pointcodeExt,
                    @pointnameEnt:=IFNULL(t2.pointname,'') AS pointnameEnt,
                    @pointnameExt:=IFNULL(t3.pointname,'') AS pointnameExt,
                    @devicecode:=IFNULL(t2.devicecode,'') AS devicecode,
                    @devicecodeExt:=IFNULL(t3.devicecode,'') AS devicecodeExt,
                    @cpicEnterPath:=t2.cpic1path AS cpicEnterPath,
                    @cpicExitPath:=t3.cpic1path AS cpicExitPath,
                    @carcolor:=IFNULL(t2.carcolor,t3.carcolor) AS carcolor,
                    @plate_position:=IFNULL(t2.plate_position,t3.plate_position) AS plate_position,
                    @collectiondate1:=IFNULL(t2.collectiondate,NULL) AS collectiondate1,   -- 入口采集时间
                    @collectiondate2:=IFNULL(t3.collectiondate,NOW()) AS collectiondate2
                FROM  park_detect_info t2
                    INNER JOIN park_detect_info t3 ON NEW.exit_nid = t3.nid
                WHERE  t2.nid = NEW.enter_nid;
    
                    IF @flag = '1' THEN
                INSERT INTO t4 SELECT
                        @flag:= '2' AS falg,
                        @licensetype:= t2.licensetype AS licensetype,
                        @carno:= t2.carno AS carno,
                        @carno_original:= t2.carno_original AS carno_original,
                        @pointcodeEnt:= '' AS pointcodeEnt,
                        @pointcodeExt:= IFNULL(t2.pointcode,'') AS pointcodeExt,
                        @pointnameEnt:= '' AS pointnameEnt,
                        @pointnameExt:= IFNULL(t2.pointname,'') AS pointnameExt,
                        @devicecode:= '' AS devicecode,
                        @devicecodeExt:= IFNULL(t2.devicecode,'') AS devicecodeExt,
                        @cpicEnterPath:= '' AS cpicEnterPath,
                        @cpicExitPath:= t2.cpic1path AS cpicExitPath,
                        @carcolor:= t2.carcolor AS carcolor,
                        @plate_position:=t2.plate_position AS plate_position,
                        @collectiondate1:= NULL AS collectiondate1,   -- 入口采集时间
                        @collectiondate2:= t2.collectiondate AS collectiondate2
                    FROM  park_detect_info t2  WHERE  t2.nid = NEW.exit_nid;         -- 如果出口存在,则用出口的
                    END IF;  
    
                            IF @flag = '1' THEN
                INSERT INTO t4 SELECT
                        @flag:= '2' AS falg,
                        @licensetype:=t2.licensetype AS licensetype,
                        @carno:= t2.carno AS carno,
                        @carno_original:= t2.carno_original AS carno_original,
                        @pointcodeExt:='' AS pointcodeExt,
                        @pointcodeEnt:=IFNULL(t2.pointcode,'') AS pointcodeEnt,
                        @pointnameEnt:=IFNULL(t2.pointname,'') AS pointnameEnt,
                        @pointnameExt:='' AS pointnameExt,
                        @devicecode:=IFNULL(t2.devicecode,'') AS devicecode,
                        @devicecodeExt:='' AS devicecodeExt,
                        @cpicEnterPath:=t2.cpic1path AS cpicEnterPath,
                        @cpicExitPath:='' AS cpicExitPath,
                        @carcolor:=t2.carcolor AS carcolor,
                        @plate_position:=t2.plate_position AS plate_position,
                        @collectiondate1:=t2.collectiondate AS collectiondate1,   
                        @collectiondate2:=NULL AS collectiondate2
                    FROM  park_detect_info t2  WHERE t2.nid = NEW.enter_nid;          -- 如果出口不存在,则用入口的
            END IF;         
    
            IF @flag = '2' THEN 
                SET NEW.`licensetype` = @licensetype; SET @licensetype:= NULL;
                SET NEW.carno = @carno; SET @carno:= NULL;
                SET NEW.carno_original = @carno_original;SET @carno_original:= NULL;
                SET NEW.pointcodeEnt = @pointcodeEnt; SET @pointcodeEnt:= NULL;  SET NEW.pointcodeExt = @pointcodeExt;SET @pointcodeExt:= NULL;
                SET NEW.pointnameEnt = @pointnameEnt; SET @pointnameEnt:= NULL;  SET NEW.pointnameExt = @pointnameExt;SET @pointnameExt:= NULL;
                SET NEW.devicecode = @devicecode;SET @devicecode:= NULL;
                SET NEW.devicecodeExt = @devicecodeExt;SET @devicecodeExt:= NULL;
                SET NEW.cpicEnterPath = @cpicEnterPath; SET @cpicEnterPath:= NULL;
                SET NEW.cpicExitPath = @cpicExitPath;SET @cpicExitPath:= NULL;
                SET NEW.carcolor = @carcolor;SET @carcolor:= NULL;
                SET NEW.plate_position = @plate_position;SET @plate_position:= NULL;
                SET NEW.collectiondate1 = @collectiondate1;SET @collectiondate1:= NULL;
                SET NEW.collectiondate2 = @collectiondate2;SET @collectiondate2:= NULL;
              SET @flag:= '1';
          END IF; 
    
            DELETE FROM t4 WHERE 1=1;
    
    END
    
    

    以前写过的一个坑爹的mysql 触发器,看看是否有参考价值。

    评论
    解决 无用
    打赏 举报
查看更多回答(2条)

相关推荐 更多相似问题