我努力来着,我原先想着是直接插入 insert into pptn select stcd,(convert(varchar(10),getdate(),120)+‘06:00:00’,drp,2.00,null,sum(drp),null from pptn where tm between convert(varchar(10),getdate()-1,120)+' 06:00:00' and convert(varchar(10),getdate(),120)+' 06:00:00' and intv=2 group by stcd 结果主键冲突,每个站号6点只能有一条 我又想了update,但是不会写,能帮我写一个吗?我急用,感激不尽
CREATE OR REPLACE TRIGGER PPTN_TRIGGER AFTER INSERT ON PPTN FOR EACH ROW BEGIN DECLARE V$DRP PPTN.DRP%TYPE; V$INTV PPTN.INTV%TYPE; CURSOR CUR_A IS SELECT INTV,SUM(DRP) FROM PPTN WHERE TM<TRUNC(SYSDATE,'HH') AND TM>=TRUNC(SYSDATE-1,'HH') GROUP BY INTV; BEGIN
IF TO_CHAR(TRUNC(SYSDATE,'HH'),'HH') = '06' THEN OPEN CUR_A; LOOP FETCH CUR_A INTO V$INTV,V$DRP; EXIT WHEN CUR_A%NOTFOUND; UPDATE PPTN SET DYP=V$DRP WHERE INTV = V$INTV AND TM = TRUNC(SYSDATE,'HH') END LOOP ; CLOSE CUR_A; END IF; END;
declare @id int, @id2 int, @tm datetime, @sumall int; select @id = STCD, @tm = TM from inserted; select @sumall = sum(drp) from pptn where tm between convert(varchar(10),@tm -1,120)+' 06:00:00' and convert(varchar(10),@tm,120)+' 06:00:00' and intv=2 and STCD = @id; update pptn set DYP = @sumall where STCD = @id and tm = convert(varchar(10),@tm -1,120)+' 06:00:00' ;
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点后执行。
;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;
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
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, 2go 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 是什么说法?
这句的意思是判断写入的数据是否有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
应该改为 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
我也是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
我努力来着,我原先想着是直接插入
insert into pptn
select stcd,(convert(varchar(10),getdate(),120)+‘06:00:00’,drp,2.00,null,sum(drp),null from pptn
where tm between convert(varchar(10),getdate()-1,120)+' 06:00:00' and convert(varchar(10),getdate(),120)+' 06:00:00'
and intv=2
group by stcd
结果主键冲突,每个站号6点只能有一条
我又想了update,但是不会写,能帮我写一个吗?我急用,感激不尽
AFTER INSERT ON PPTN
FOR EACH ROW
BEGIN
DECLARE
V$DRP PPTN.DRP%TYPE;
V$INTV PPTN.INTV%TYPE;
CURSOR CUR_A
IS
SELECT INTV,SUM(DRP)
FROM PPTN
WHERE TM<TRUNC(SYSDATE,'HH')
AND TM>=TRUNC(SYSDATE-1,'HH')
GROUP BY INTV;
BEGIN
IF TO_CHAR(TRUNC(SYSDATE,'HH'),'HH') = '06'
THEN
OPEN CUR_A;
LOOP
FETCH CUR_A INTO V$INTV,V$DRP;
EXIT WHEN CUR_A%NOTFOUND;
UPDATE PPTN
SET DYP=V$DRP
WHERE INTV = V$INTV
AND TM = TRUNC(SYSDATE,'HH')
END LOOP ;
CLOSE CUR_A;
END IF;
END;
select @id = STCD, @tm = TM from inserted;
select @sumall = sum(drp) from pptn
where tm between convert(varchar(10),@tm -1,120)+' 06:00:00' and convert(varchar(10),@tm,120)+' 06:00:00'
and intv=2 and STCD = @id;
update pptn set DYP = @sumall
where STCD = @id
and tm = convert(varchar(10),@tm -1,120)+' 06:00:00' ;
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点后执行。
可以把你修改后的 语句贴出来
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
为什么没更新?
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加起来的值。)
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
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有数的记录
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, 2go
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
真心给您添麻烦了,我也试了一下存储过程,就是定时作业6点05的时候把60条记录在pptn表里update,不过还没成功,不过我会努力地,有这么多老师帮助我,很有信心啊
触发器我又改了一下,可能更还理解一点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
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
输出结果
-------------------------------------------------------------------------
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
老师,我用的sql2000,怎么一插入TM 是2014-05-16 00:00:00 ,总是只插入日期了,00:00:00没有哎,sql2008就不那样,sql2008就能插入零点的时间。
老师,我QQ是873684592,能加一下您QQ吗?嘿嘿
考虑到一次可能插入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