--> --> (Roy)生成測試數據
declare @T table([c1] nvarchar(1),[c2] int,[c3] int,[c4] int)
Insert @T
select N'a',2,6,8 union all
select N'a',8,7,15 union all
select N'b',1,6,7 union all
select N'b',3,-5,-2 union all
select N'b',-2,3,1 union all
select N'c',1,3,4 union all
select N'c',3,1,4 union all
select N'c',4,5,9
;with c
as
(Select *,row=row_number()over(order by [c1]) from @T )
select
*
from
c t
where
not exists(select 1 from c where [c1]=t.[c1] and [c4]=t.[c2] and row+1=t.row )
and
row>(select min(row) from c where [c1]=t.[c1])
(8 行受影响)
c1 c2 c3 c4 row
---- ----------- ----------- ----------- --------------------
b 3 -5 -2 4
c 3 1 4 7(2 行受影响)
declare @T table([c1] nvarchar(1),[c2] int,[c3] int,[c4] int)
Insert @T
select N'a',2,6,8 union all
select N'a',8,7,15 union all
select N'b',1,6,7 union all
select N'b',3,-5,-2 union all
select N'b',-2,3,1 union all
select N'c',1,3,4 union all
select N'c',3,1,4 union all
select N'c',4,5,9
;with c
as
(Select *,row=row_number()over(order by [c1]) from @T )
select
*
from
c t
where
not exists(select 1 from c where [c1]=t.[c1] and [c4]=t.[c2] and row+1=t.row )
and
row>(select min(row) from c where [c1]=t.[c1])
(8 行受影响)
c1 c2 c3 c4 row
---- ----------- ----------- ----------- --------------------
b 3 -5 -2 4
c 3 1 4 7(2 行受影响)
as
(Select *,row=row_number()over(order by [c1]) from @T )
select
*
from
c t
where
not exists(select 1 from c where [c1]=t.[c1] and [c4]=t.[c2] and row+1=t.row )
and
row>(select min(row) from c where [c1]=t.[c1]) and [c2]+[c3]=[c4]--加上[c2]+[c3]=[c4]条件
2000只可用表变量或临时表处理(自增列判断出上一行数据)
insert #tb
select 'a', 2, 6, 8 union all
select 'a', 8, 7, 15 union all
select 'b', 1, 6, 7 union all
select 'b', 3, -5, -2 union all ---问题行
select 'b', -2, 3, 1 union all
select 'c', 1, 3, 4 union all
select 'c', 3, 1, 4 union all ---问题行
select 'c', 4, 5, 9 select tid=Identity(int,1,1),* into # from #tb select c1,c2,c3,c4 from # a where exists(select * from # where tid=a.tid-1 and c1=a.c1 and c4<>a.c2)
c1 c2 c3 c4
----- ----------- ----------- -----------
b 3 -5 -2
c 3 1 4(所影响的行数为 2 行)update的方法没想到,要休息了...
接4楼:update语句update t set c2=isnull((select c4 from # where c1=t.c1 and tid=t.tid-1),c2) from # t
没有考虑到条件c4=c2+c3
--试试这个
while exists(select * from # a where exists(select * from # where tid=a.tid-1 and c1=a.c1 and c4<>a.c2))
begin
update t set c2=isnull((select c4 from # where c1=t.c1 and tid=t.tid-1),c2),
C4=isnull((select c4 from # where c1=t.c1 and tid=t.tid-1),c2)+C3 from # t
end
楼上的朋友可行,结贴给分!
再次感谢一下给予帮助的朋友们顺便再提一下,我想知道数据库中 所以触发器或是存储过程对某表如table1有插入或更改操作,有什么方法吗?
create trigger tri on tb for insert,update
as
begin
select * from inserted
end
go
不知道我表述的是不是很清楚
表table1,整个数据库对这个table1有操作的 存储过程或触发器 ,我不想一一查找这些代码,有没有便捷的方法?