表A里的数据字段1 字段2 字段3 字段4 字段5
a 120 100 100 100
a 0 50 120 80
a 100 20 60 100表B里的数据是字段1 字段2 字段3 字段4 字段5
a 150 170 80 160要求将 A表里数据同B表里的数据减 得到的结果是字段1 字段2 字段3 字段4 字段5
a 0 0 20 0
a 0 0 120 20
a 70 0 60 100请高手赐教,谢谢!
a 120 100 100 100
a 0 50 120 80
a 100 20 60 100表B里的数据是字段1 字段2 字段3 字段4 字段5
a 150 170 80 160要求将 A表里数据同B表里的数据减 得到的结果是字段1 字段2 字段3 字段4 字段5
a 0 0 20 0
a 0 0 120 20
a 70 0 60 100请高手赐教,谢谢!
解决方案 »
- 在成绩管理存储过程中,最低分数、最高分数的计算不允许使用系统函数(包括
- SQL2000 附加数据库错误0,找不到指定模块(126)
- 单行转多行
- 菜鸟求个触发器的写法~谢谢
- HELP
- 急! ! ! ! 急! ! ! ! 急! ! ! ! 提取表中头10条,头11-20,21-30条,31-40条数据按id降序
- 有高手吗:一张订单表有很多客户订单信息,求订单时间距现在超过6个月的客户
- 帮我,【为您冲话费】您们好,遇到难题了,表单多条数据,插入不成功,变成逗号相连在一个字段了,今天坐等5小时
- 如何一次性清除数据库里嵌入代码啊?
- 如何用SQL语句写一个月的第一天,和最后一天?
- 从字符到日期
- 求一SQL?
except
select * from B
如: B表里字段2是150 A表里分别是120,0,100
150-120=30 >0设置120 为 0 ,比较下一条 30-0=30 >0 设置0 为 0 ,比较下一条 30-100=-70 <0 设置 100为 70 结束。
字段3、字段4、字段5 同理。
INSERT @TA
SELECT 'a', 120, 100, 100, 100 UNION ALL
SELECT 'a', 0, 50, 120, 80 UNION ALL
SELECT 'a', 100, 20, 60, 100DECLARE @TB TABLE(COL1 VARCHAR(1),COL2 INT,COL3 INT,COL4 INT,COL5 INT)
INSERT @TB
SELECT 'a', 150, 170, 80, 160
SELECT COL1,
CASE WHEN SUM2>S2 THEN CASE WHEN SUM22>S2 THEN COL2 ELSE SUM2-S2 END ELSE 0 END AS COL2,
CASE WHEN SUM3>S3 THEN CASE WHEN SUM33>S3 THEN COL3 ELSE SUM3-S3 END ELSE 0 END AS COL3,
CASE WHEN SUM4>S4 THEN CASE WHEN SUM44>S4 THEN COL4 ELSE SUM4-S4 END ELSE 0 END AS COL4,
CASE WHEN SUM5>S5 THEN CASE WHEN SUM55>S5 THEN COL5 ELSE SUM5-S5 END ELSE 0 END AS COL5
FROM (
SELECT A.COL1,A.COL2,A.COL3,A.COL4,A.COL5,
(SELECT SUM(COL2) FROM @TA WHERE ID<=A.ID) AS SUM2,
(SELECT SUM(COL3) FROM @TA WHERE ID<=A.ID) AS SUM3,
(SELECT SUM(COL4) FROM @TA WHERE ID<=A.ID) AS SUM4,
(SELECT SUM(COL5) FROM @TA WHERE ID<=A.ID) AS SUM5, (SELECT SUM(COL2) FROM @TA WHERE ID<A.ID) AS SUM22,
(SELECT SUM(COL3) FROM @TA WHERE ID<A.ID) AS SUM33,
(SELECT SUM(COL4) FROM @TA WHERE ID<A.ID) AS SUM44,
(SELECT SUM(COL5) FROM @TA WHERE ID<A.ID) AS SUM55,
B.COL2 AS S2, B.COL3 AS S3, B.COL4 AS S4, B.COL5 AS S5
FROM @TA AS A JOIN @TB AS B ON A.COL1=B.COL1
) T
/*
COL1 COL2 COL3 COL4 COL5
---- ----------- ----------- ----------- -----------
a 0 0 20 0
a 0 0 120 20
a 70 0 60 100
*/
Create table #A(字段1 varchar(10),字段2 int,字段3 int,字段4 int,字段5 int)
insert #A(字段1,字段2,字段3,字段4,字段5)
select 'a' , 120, 100 , 100, 100
union all select 'a' , 0 , 50 ,120 , 80
union all select 'b',120,20,20,20
union all select 'a' , 100 , 20 , 60 , 100Create table #B(字段1 varchar(10),字段2 int,字段3 int,字段4 int,字段5 int)
insert #B(字段1,字段2,字段3,字段4,字段5)
select 'a' , 150 , 170 , 80 , 160
union all select 'b',100,10,10,10declare @count int
select ID=identity(int,1,1),* into #AA from #A order by 字段1 select ID=identity(int,1,1),* into #BB from #B order by 字段1
set @count=@@identitydeclare @字段1 varchar(15),@字段2 int,@字段3 int,@字段4 int,@字段5 int
declare @字段11 varchar(15),@字段22 int,@字段33 int,@字段44 int,@字段55 int
while @count>0
begin
select @字段1=字段1,@字段2=字段2,@字段3=字段3,@字段4=字段4,@字段5=字段5 from #BB where ID=@count
update #AA
set 字段2=@字段22,字段3=@字段33,字段4=@字段44,字段5=@字段55
,@字段22=(case when @字段1=字段1 then case when 字段2>@字段2 then 字段2-@字段2 else 0 end else 字段2 end)
,@字段2=(case when @字段1=字段1 then case when 字段2>@字段2 then 0 else @字段2-字段2 end else @字段2 end)
,@字段33=(case when @字段1=字段1 then case when 字段3>@字段3 then 字段3-@字段3 else 0 end else 字段3 end)
,@字段3=(case when @字段1=字段1 then case when 字段3>@字段3 then 0 else @字段3-字段3 end else @字段3 end)
,@字段44=(case when @字段1=字段1 then case when 字段4>@字段4 then 字段4-@字段4 else 0 end else 字段4 end)
,@字段4=(case when @字段1=字段1 then case when 字段4>@字段4 then 0 else @字段4-字段4 end else @字段4 end)
,@字段55=(case when @字段1=字段1 then case when 字段5>@字段5 then 字段5-@字段5 else 0 end else 字段5 end)
,@字段5=(case when @字段1=字段1 then case when 字段5>@字段5 then 0 else @字段5-字段5 end else @字段5 end)
-- ,@字段1=字段1
set @count=@count-1
end
select * from #AAdrop table #AA
drop table #BB
drop table #A
drop table #B
------------------------
ID 字段1 字段2 字段3 字段4 字段5
----------- ---------- ----------- ----------- ----------- -----------
1 a 0 0 20 0
2 a 0 0 120 20
3 a 70 0 60 100
4 b 20 10 10 10(所影响的行数为 4 行)
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([字段1] varchar(1),[字段2] int,[字段3] int,[字段4] int,[字段5] int)
insert [ta]
select 'a',120,100,100,100 union all
select 'a',0,50,120,80 union all
select 'a',100,20,60,100
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] int,[字段3] int,[字段4] int,[字段5] int)
insert [tb]
select 'a',150,170,80,160select * from [ta]
select * from [tb]select id=identity(int,1,1),* into # from taselect a.[字段1]
,[字段2]=case when a.sum2<=b.[字段2] then 0 when a.sum2>b.[字段2]+a.[字段2] then a.[字段2] else a.sum2-b.[字段2] end
,[字段3]=case when a.sum3<=b.[字段3] then 0 when a.sum3>b.[字段3]+a.[字段3] then a.[字段3] else a.sum3-b.[字段3] end
,[字段4]=case when a.sum4<=b.[字段4] then 0 when a.sum4>b.[字段4]+a.[字段4] then a.[字段4] else a.sum4-b.[字段4] end
,[字段5]=case when a.sum5<=b.[字段5] then 0 when a.sum5>b.[字段5]+a.[字段5] then a.[字段5] else a.sum5-b.[字段5] end
from
(
select a.*
,sum2=(select sum([字段2]) from # where [字段1]=a.[字段1] and id<=a.id)
,sum3=(select sum([字段3]) from # where [字段1]=a.[字段1] and id<=a.id)
,sum4=(select sum([字段4]) from # where [字段1]=a.[字段1] and id<=a.id)
,sum5=(select sum([字段5]) from # where [字段1]=a.[字段1] and id<=a.id)
from # a
) a
join tb b
on a.[字段1]=b.[字段1]--测试结果:
/*
字段1 字段2 字段3 字段4 字段5
---- ----------- ----------- ----------- -----------
a 0 0 20 0
a 0 0 120 20
a 70 0 60 100(3 行受影响)
*/
drop table #