求一比较函数 或者给个思路也可以
表名: Tt
字段: id , str1(nvarchar),jg(int),zd (int), ddate (datetime)
-- ----------- ------ ------- --------------记录: 1 abc 3000 null 2006-7-25
2 abd 3100 null 2006-7-25
插入:
3 abc 3050 null 2006-7-26
4 abd 3300 null 2006-7-26
结果:
(表Tt) 1 abc 3000 null 2006-7-25
2 abd 3100 null 2006-7-25
3 abc 3050 50 2006-7-26
4 abd 3300 200 2006-7-26 "zd"的值应该是(自动) 当天的价格 -昨天的价格(jg) 并且str1 相同 (时间为:周二到周五)
当天的价格-周五的价格(jg) 并且str1 相同 (时间为:周一)我这里不是要查询 是直接写入数据库
表名: Tt
字段: id , str1(nvarchar),jg(int),zd (int), ddate (datetime)
-- ----------- ------ ------- --------------记录: 1 abc 3000 null 2006-7-25
2 abd 3100 null 2006-7-25
插入:
3 abc 3050 null 2006-7-26
4 abd 3300 null 2006-7-26
结果:
(表Tt) 1 abc 3000 null 2006-7-25
2 abd 3100 null 2006-7-25
3 abc 3050 50 2006-7-26
4 abd 3300 200 2006-7-26 "zd"的值应该是(自动) 当天的价格 -昨天的价格(jg) 并且str1 相同 (时间为:周二到周五)
当天的价格-周五的价格(jg) 并且str1 相同 (时间为:周一)我这里不是要查询 是直接写入数据库
create table tt
(
id int identity(1,1),
str1 nvarchar(10),
jg int,
zd int,
ddate datetime
)
insert into tt
select 'abc',3000,null,'2005-7-25' union all
select 'abcd',3100,null,'2005-7-25'
go
create trigger tr_sample on tt
instead of insert
as
begin
insert into tt
select str1,jg,jg-(select top 1 jg from tt where str1=i.str1 order by ddate desc),ddate
from inserted i
end
go
insert into tt(str1,jg,ddate)
select 'abc',3050,'2005-7-26' union all
select 'abcd',3300,'2005-7-26'select *
from ttgo
drop table tt/*id str1 jg zd ddate
----------- ---------- ----------- ----------- ------------------------------------------------------
1 abc 3000 NULL 2005-07-25 00:00:00.000
2 abcd 3100 NULL 2005-07-25 00:00:00.000
3 abc 3050 50 2005-07-26 00:00:00.000
4 abcd 3300 200 2005-07-26 00:00:00.000
*/
select 'abc',3000,null,'2005-7-25' union all
select 'abcd',3100,null,'2005-7-25'
go
create trigger tr_sample on tt
instead of insert
as
begin
insert into tt
select str1,jg,jg-(select top 1 jg from tt where str1=i.str1 order by ddate desc),ddate
from inserted i
end能给我解释一下这个语句吗?
instead of insert
as
begin
insert into tt
select str1,jg,jg-(select top 1 jg from tt where str1=i.str1 order by ddate desc),ddate
from inserted i
end--you should replace the tablename/column name/trigger name which fit your own table other than the test data--for more information, suggest than you should read the books online and learn to search with google