求高手帮忙写一个触发器,小妹跪求啊

sunchao643 2014-05-15 02:07:59
我们单位有60个测站,站号是STCD,每两个小时每个测站会向数据库rwdb表PPTN发送一条数据,PPTN表结构(其中STCD、TM是主键,也就是说不能出现同一时间的重复记录)
STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH

31128350 2014-05-10 00:00:00 1 2.00 null null null
31128350 2014-05-10 02:00:00 0 2.00 null null null
31128350 2014-05-10 04:00:00 15 2.00 null null null
31128350 2014-05-10 06:00:00 6 2.00 null null null
。。。
31128350 2014-05-10 22:00:00 0 2.00 null null null
41820100 2014-05-10 00:00:00 1 2.00 null null null
41820100 2014-05-10 02:00:00 0 2.00 null null null
。。。
我想实现功能:每天早上6点数据库接收到每个站06:00:00的数据后,就自动计算每一个站点的日雨量dyp(也就是昨天6点到今天16点。tm>='2014-05-14 06:00:00' and <'2014-05-15 06:00:00'intv=2的所有drp加起来的值。),计算后把每个站点原来的6点的记录更新成
STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH
31128350 2014-05-10 06:00:00 6 2.00 null 16 null
触发器怎么写?请帮帮我,想了一早上还是没想出来,昨天因为实验还把数据库里的数据搞没了
...全文
276 点赞 收藏 29
写回复
29 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
在路上_- 2014-05-16
我也是菜鸟,我一般不用QQ。对不起了
回复
在路上_- 2014-05-16
我也是SQL Server 2000,没有你说的问题。建议你到 SQL查询分析器 里面试一下 考虑到一次可能插入60条数据,下面这个可能效率更高一点儿
CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )
  
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
AFTER INSERT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
  BEGIN
    UPDATE tPPTN
    SET DYP=t.DYP
    FROM tPPTN a INNER JOIN
       (
        SELECT i.STCD, i.TM, SUM(p.DRP) AS DYP
        FROM tPPTN p INNER JOIN inserted i
            ON p.STCD=i.STCD AND p.INTV=2 AND DATEPART(hh, i.TM)=6
        WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
        GROUP BY i.STCD, i.TM
       ) t ON a.STCD=t.STCD AND a.TM=t.TM
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128355, '2014-05-11 08:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN ORDER BY STCD, TM
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN
回复
sunchao643 2014-05-16
太强了,老师,哇咔咔,恩人啊,深深地佩服 老师,我用的sql2000,怎么一插入TM 是2014-05-16 00:00:00 ,总是只插入日期了,00:00:00没有哎,sql2008就不那样,sql2008就能插入零点的时间。 老师,我QQ是873684592,能加一下您QQ吗?嘿嘿
回复
在路上_- 2014-05-16
SELECT * FROM tPPTN ORDER BY STCD, TM 输出结果 ------------------------------------------------------------------------- 31128350 2014-05-10 12:00:00 1 2 NULL 31128350 2014-05-10 14:00:00 0 2 NULL 31128350 2014-05-10 16:00:00 4 2 NULL 31128350 2014-05-10 18:00:00 2 2 NULL 31128350 2014-05-10 20:00:00 0 2 NULL 31128350 2014-05-10 22:00:00 6 2 NULL 31128350 2014-05-11 00:00:00 3 2 NULL 31128350 2014-05-11 02:00:00 2 2 NULL 31128350 2014-05-11 04:00:00 1 2 NULL 31128350 2014-05-11 06:00:00 0 2 19 31128355 2014-05-10 16:00:00 1 2 NULL 31128355 2014-05-10 18:00:00 0 2 NULL 31128355 2014-05-10 22:00:00 3 2 NULL 31128355 2014-05-11 06:00:00 1 2 4 31128355 2014-05-11 08:00:00 2 2 NULL
回复
在路上_- 2014-05-16
应该改为 AFTER 触发器,否则还要加上 ELSE 处理非6点钟写入数据的情况。从效率上考虑,改为 AFTER 触发器
CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )
 
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
AFTER INSERT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
  BEGIN
    UPDATE tPPTN
    SET DYP=(SELECT SUM(DRP) FROM tPPTN p
             WHERE p.STCD=a.STCD AND p.INTV=2
               AND p.TM>=DATEADD(day,-1,a.TM) AND p.TM<a.TM
            )
    FROM tPPTN a
      INNER JOIN inserted i
        ON a.STCD=i.STCD AND a.TM=i.TM AND DATEPART(hh, i.TM)=6
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128355, '2014-05-11 08:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN
回复
在路上_- 2014-05-16
这句的意思是判断写入的数据是否有6点钟的。select 1=select STCD 或 select 任意一个字段。 触发器我又改了一下,可能更还理解一点
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
  BEGIN
    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, i.INTV, SUM(p.DRP)
    FROM tPPTN p
      INNER JOIN inserted i
        ON p.STCD=i.STCD AND p.INTV=2 AND DATEPART(hh, i.TM)=6
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
    GROUP BY i.STCD, i.TM, i.DRP, I.INTV
  END
END
回复
sunchao643 2014-05-16
引用 21 楼 xxzxwsx 的回复:
经测试,这个可以

CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )
 
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2

go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
  BEGIN
    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, i.INTV, SUM(p.DRP)
    FROM tPPTN p
      INNER JOIN (SELECT * FROM inserted WHERE DATEPART(hh, TM)=6) i
      ON p.STCD=i.STCD AND p.INTV=2
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
    GROUP BY i.STCD, i.TM, i.DRP, I.INTV
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN

IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)老师 select 1 是什么说法?
回复
sunchao643 2014-05-16
引用 20 楼 xxzxwsx 的回复:
如果软件每次用一条语句写入一条记录,以上触发器没问题。如果一次写入多条记录,以上触发器有问题。容我再想想

好滴,谢谢老师,有一点我没说清楚因为stcd和tm是共同主键,所以pptn表不能出现相同站号stcd同一时间的两条记录
真心给您添麻烦了,我也试了一下存储过程,就是定时作业6点05的时候把60条记录在pptn表里update,不过还没成功,不过我会努力地,有这么多老师帮助我,很有信心啊
回复
在路上_- 2014-05-16
经测试,这个可以

CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )
 
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2

go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
  BEGIN
    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, i.INTV, SUM(p.DRP)
    FROM tPPTN p
      INNER JOIN (SELECT * FROM inserted WHERE DATEPART(hh, TM)=6) i
      ON p.STCD=i.STCD AND p.INTV=2
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
    GROUP BY i.STCD, i.TM, i.DRP, I.INTV
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN

回复
在路上_- 2014-05-16
如果软件每次用一条语句写入一条记录,以上触发器没问题。如果一次写入多条记录,以上触发器有问题。容我再想想
回复
在路上_- 2014-05-16
那个触发器可以满足你的要求呀。一次进60条应该没有问题
回复
sunchao643 2014-05-16
引用 16 楼 xxzxwsx 的回复:

CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )

INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 2, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF (SELECT DATEPART(hh, TM) FROM inserted)=6
  BEGIN
    DECLARE @dyp int
    SELECT @dyp=SUM(p.DRP)
    FROM tPPTN p INNER JOIN inserted i ON p.STCD=i.STCD
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM AND p.INTV=2

    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, I.INTV, @dyp
    FROM inserted i
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN

其实老师我还有个问题,如果每次给数据库更新2小时数据的软件,一下进了这60个站的60条数据,会不会触发触发器出问题,没法更新这60条6点数据
回复
sunchao643 2014-05-16
引用 16 楼 xxzxwsx 的回复:

CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )

INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 2, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF (SELECT DATEPART(hh, TM) FROM inserted)=6
  BEGIN
    DECLARE @dyp int
    SELECT @dyp=SUM(p.DRP)
    FROM tPPTN p INNER JOIN inserted i ON p.STCD=i.STCD
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM AND p.INTV=2

    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, I.INTV, @dyp
    FROM inserted i
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN

老师,谢谢您帮助我,是我没讲明白,其实这些记录 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null null null 1128350 2014-05-10 00:00:00 1 2.00 null null null 31128350 2014-05-10 02:00:00 0 2.00 null null null 31128350 2014-05-10 04:00:00 15 2.00 null null null 31128350 2014-05-10 06:00:00 6 2.00 null null null 。。。 31128350 2014-05-10 22:00:00 0 2.00 null null null 41820100 2014-05-10 00:00:00 1 2.00 null null null 41820100 2014-05-10 02:00:00 0 2.00 null null null 。。。 。。。 41820100 2014-05-16 06:00:00 0 2.00 null null null 41803200 2014-05-15 06:00:00 0 2.00 null null null 41803200 2014-05-15 06:00:00 0 2.00 null null null 都是一个软件动态写进数据库表pptn里面的,两小时一条,stcd一共有60个这样的8位码代表的测站,不仅仅是31128350和41820100这两个站码,我现在想做的是,每天只要有哪个8位码在pptn里有6点的记录,就触发触发器立即把6点的这条记录更新成dyp有数的记录
回复
在路上_- 2014-05-16

CREATE TABLE tPPTN(STCD int,
  TM smalldatetime,
  DRP int,
  INTV int,
  DYP int
 )

INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 2, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
  IF (SELECT DATEPART(hh, TM) FROM inserted)=6
  BEGIN
    DECLARE @dyp int
    SELECT @dyp=SUM(p.DRP)
    FROM tPPTN p INNER JOIN inserted i ON p.STCD=i.STCD
    WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM AND p.INTV=2

    INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
    SELECT i.STCD, i.TM, i.DRP, I.INTV, @dyp
    FROM inserted i
  END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN

回复
sunchao643 2014-05-16
引用 13 楼 xdashewan 的回复:
STCD明显不一样,这也要更新?
不是,这些记录 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null null null 。。。 41820100 2014-05-16 06:00:00 0 2.00 null null null 都是一个软件自动写进数据库的,这些stcd都是固定的,我想写一个存储过程,每天六点01分的时候把每个stcd当天6点的记录更新(其实就是最先进来的STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR WTH值都不变,光把dyp更新一下,dyp是昨天6点到今天16点。tm>='2014-05-14 06:00:00' and <'2014-05-15 06:00:00'intv=2的所有drp加起来的值。) 然后每天所有当天6点的记录就都更新成dyp有数了 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null 16 null 。。。 41820100 2014-05-16 06:00:00 0 2.00 null 0 null
回复
sunchao643 2014-05-16
不是,这些记录 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null null null 。。。 41820100 2014-05-16 06:00:00 0 2.00 null null null 都是一个软件自动写进数据库的,这些stcd都是固定的,我想写一个存储过程,每天六点01分的时候把每个stcd6点的记录更新(其实就是最先进来的STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR WTH值都不变,光把dyp更新一下,dyp是昨天6点到今天16点。tm>='2014-05-14 06:00:00' and <'2014-05-15 06:00:00'intv=2的所有drp加起来的值。)
回复
xdashewan 2014-05-16
STCD明显不一样,这也要更新?
回复
sunchao643 2014-05-16
create procedure test as begin ;with cte as ( select stcd,sum(drp) as DRP from pptn where tm>=cast( convert(varchar(10),dateadd(day,-1,getdate()),120)+ ' 06:00:00' as datetime) and tm<cast( convert(varchar(10),getdate(),120)+ ' 06:00:00' as datetime) and intv=2 group by stcd ) update A set A.DYP=B.DRP from pptn as A inner join cte as B on A.stcd=B.stcd where A.tm=cast( convert(varchar(10),getdate,120)+ ' 06:00:00' as datetime) and A.intv=2; end 我执行了之后,表pptn里面6点的记录dyp都没有变化,这是怎么回事 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null null null 。。。 41820100 2014-05-16 06:00:00 0 2.00 null null null 执行之后不是应该所有6点的记录更新成 STCD(站号) TM(时间) DRP(每两小时雨量) INTV(标志) PDR DYP(日雨量) WTH 31128350 2014-05-16 06:00:00 6 2.00 null 16 null 。。。 41820100 2014-05-16 06:00:00 0 2.00 null 0 null 为什么没更新?
回复
闹铃 2014-05-15
引用 10 楼 sunchao643 的回复:
[quote=引用 9 楼 sunchao643 的回复:] [quote=引用 7 楼 denghui_li 的回复:]


create procedure test
as
begin

;with cte as
(
select stcd,sum(drp) as DRP
from table
where tm>=cast( convert(varchar(10),dateadd(day,-1,getdate()),120)+ ' 06:00:00' as datetime) and <cast( convert(varchar(10),getdate(),120)+ ' 06:00:00' as datetime) and intv=2
group by stcd
)
update A
set A.DYP=B.DRP
from table as A
inner join cte as B on A.stcd=B.stcd
where A.tm=cast( convert(varchar(10),getdate,120)+ ' 06:00:00' as datetime) and A.intv=2;

end

--创建作业 调用过程 ,在每天 6点后执行。

就一个表PPTN,老师,A和B是什么意思?我想在PPTN原表上更新每个STCD的6点的值[/quote]with和table的地方都有错啊[/quote] cte中 where 后面 少写一上 tm ,因为语句没有测试,简单的 语法错误 你修改一下 table 就是 表PPTN cte 是临时表 a,b 都别名 可以把你修改后的 语句贴出来
回复
sunchao643 2014-05-15
引用 9 楼 sunchao643 的回复:
[quote=引用 7 楼 denghui_li 的回复:]


create procedure test
as
begin

;with cte as
(
select stcd,sum(drp) as DRP
from table
where tm>=cast( convert(varchar(10),dateadd(day,-1,getdate()),120)+ ' 06:00:00' as datetime) and <cast( convert(varchar(10),getdate(),120)+ ' 06:00:00' as datetime) and intv=2
group by stcd
)
update A
set A.DYP=B.DRP
from table as A
inner join cte as B on A.stcd=B.stcd
where A.tm=cast( convert(varchar(10),getdate,120)+ ' 06:00:00' as datetime) and A.intv=2;

end

--创建作业 调用过程 ,在每天 6点后执行。

就一个表PPTN,老师,A和B是什么意思?我想在PPTN原表上更新每个STCD的6点的值[/quote]with和table的地方都有错啊
回复
加载更多回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2014-05-15 02:07
社区公告
暂无公告