非常头疼的sql，超难，怎么写啊？sqlsql？

Question

create table tb1(a varchar(50),b varchar(50),c int,d datetime)
insert into tb1
select '','a001',-100,'2011-3-1' union all
select '','a001',-50, '2011-4-1' union all
select '','a002',-40, '2011-5-1' union all
select '','a002',-40, '2011-6-1' union all
select '','a002',-80, '2011-6-1' 
create table tb2(a varchar(50),b varchar(50),c int,d datetime)
insert into tb2
select 's0','a001',30,'2011-2-25' union all
select 's1','a001',40, '2011-2-25' union all
select 's2','a001',50, '2011-2-25' union all
select 's3','a001',80, '2011-2-25' union all
select 's4','a002',60, '2011-10-30' union all
select 's5','a002',40, '2011-5-30' union all
select 's6','a002',50, '2011-4-20' union all
select 's7','a002',30, '2011-4-25' union all
select 's8','a002',30, '2011-4-10' 
select * from tb1
select * from tb2逻辑上是这样的
从tb1的第一条循环起，比如第一条数据 ''  a001 -100 2011-03-01 00:00:00.000
找出tb2中 tb2.b=tb1.b并且tb2.d<=tb1.d中的数据
s0 a001 30 2011-02-25 00:00:00.000
s1 a001 40 2011-02-25 00:00:00.000
s2 a001 50 2011-02-25 00:00:00.000
s3 a001 80 2011-02-25 00:00:00.000然后从第一行开始检测tb2 中的 tb2.c
若第一行中的c+tb1.c>=0 就更新tb1中的a，set tb1.a='s0'
若上面的相加<0
则把tb2中的一行、二行 的c相加，再加上 tb1.c,若大于0，则 tb1.a='s0,s1'
若仍然小于0
则开始循环第三行 直到找到满足条件的行数，来更新tb1.a
若确实找不到则不跟新。
最后得到的tb1的结果是这样的s0,s1,s2      a001 -100 2011-3-1
s0,s1         a001 -50 2011-4-1
s6 a002 -40 2011-5-1
s5 a002 -30 2011-6-1
s5,s6 a002 -80 2011-6-1

Answer 1

你这更新的表至少要搞个主键啊 更新的时候没主键怎么知道更新哪一行的declare @name varchar(50)
declare @tab1_b varchar(50)
declare @tab1_c int
declare @tab2_c int
declare @tab2_a varchar(50)
declare @sum_num int
declare my_cursor1 cursor for
  select b,c  from tb1
  open my_cursor1
  fetch next from my_cursor1 into @tab1_b,@tab1_c
  while(@@fetch_status=0)
  begin
     set @sum_num=@tab1_c 
     set @name='' 
  declare my_cursor2 cursor for
  select c,a  from tb2 where b=@tab1_b
  open my_cursor2
  fetch next from my_cursor2 into @tab2_c,@tab2_a
  while(@@fetch_status=0)
  begin
 @sum_num=@sum_num+@tab2_c
       @name=@name+@tab2_a+','
       if(@sum_num>0)
        begin
         @name=left(@name,len(@name)-1)
         break
        end
  end 
fetch next from my_cursor2 into @tab2_c,@tab2_a
  end
  close my_cursor2 
  deallocate my_cursor2  
     update tb1
     set a= @name 
     where b=@tab1_b and c=@tab1_c
  end 
   fetch next from my_cursor1 into @tab1_b,@tab1_c
  end
  close my_cursor1 
  deallocate my_cursor1  

Answer 2

这个问题很纠结啊。关键在于你要循环。比如第一条s012就够了，s3就不需要了。
并且你这里也缺少一个排序的关键字。
我是不是可以认为  s1 s2 s3  也算是满足要求啊。。

Answer 3

我没环境 来测试 抱什么错误
你tab1最好有主键。

Answer 4

drop table tb1create table tb1([id] [int] IDENTITY (1, 1) NOT NULL ,  a varchar(50),b varchar(50),c int,d datetime)
insert into tb1(a,b,c,d)
select  '','a001',-100,'2011-3-1' union all
select '','a001',-50, '2011-4-1' union all
select '','a002',-40, '2011-5-1' union all
select '','a002',-40, '2011-6-1' union all
select '','a002',-80, '2011-6-1' 
create table tb2(a varchar(50),b varchar(50),c int,d datetime)
insert into tb2
select 's0','a001',30,'2011-2-25' union all
select 's1','a001',40, '2011-2-25' union all
select 's2','a001',50, '2011-2-25' union all
select 's3','a001',80, '2011-2-25' union all
select 's4','a002',60, '2011-10-30' union all
select 's5','a002',40, '2011-5-30' union all
select 's6','a002',50, '2011-4-20' union all
select 's7','a002',30, '2011-4-25' union all
select 's8','a002',30, '2011-4-10' 设置了一个主键， 下面的两个 end 处有错误，然后死循环

Answer 5

create table tb1(a varchar(50),b varchar(50),c int,d datetime)
insert into tb1
select '','a001',-100,'2011-3-1' union all
select '','a001',-50, '2011-4-1' union all
select '','a002',-40, '2011-5-1' union all
select '','a002',-40, '2011-6-1' union all
select '','a002',-80, '2011-6-1' 
create table tb2(a varchar(50),b varchar(50),c int,d datetime)
insert into tb2
select 's0','a001',30,'2011-2-25' union all
select 's1','a001',40, '2011-2-25' union all
select 's2','a001',50, '2011-2-25' union all
select 's3','a001',80, '2011-2-25' union all
select 's4','a002',60, '2011-10-30' union all
select 's5','a002',40, '2011-5-30' union all
select 's6','a002',50, '2011-4-20' union all
select 's7','a002',30, '2011-4-25' union all
select 's8','a002',30, '2011-4-10' 
with cte as 
(
select *,
(select sum(c) from tb2 a where a.b=tb2.b and a.a<=tb2.a)as SC,
SA=stuff((select ','+ltrim(a) from tb2 a where  a.b=tb2.b and a.a<=tb2.a for xml path('')),1,1,'')
from tb2 
)update tb1
set tb1.a=cte.SA
from cte 
where tb1.b=cte.b and tb1.c+cte.SC>0
AND NOT exists 
(select 1 from cte c where c.b=cte.b and tb1.c+c.SC>0 and c.a<cte.a)select * from tb1/*
a        b        c           d
-------------------------------------------------- --
s0,s1,s2  a001   -100        2011-03-01 00:00:00.000
s0,s1     a001   -50         2011-04-01 00:00:00.000
s4        a002   -40         2011-05-01 00:00:00.000
s4        a002   -40         2011-06-01 00:00:00.000
s4,s5     a002   -80         2011-06-01 00:00:00.000(5 行受影响)
*/

Answer 6

s6        a002    -40    2011-5-1
s5        a002    -30    2011-6-1
s5,s6        a002    -80    2011-6-1楼主检查一下这上面的结果正确与否，与你描述的操作情况不符

Answer 7

能换成sql2000吗？我执行不了

Answer 8

需要  order by tb2.b, tb2.d  desc排序
数据还有问题

Answer 9

use tempdb
GO
if object_id('tb1') Is Not Null
Drop Table tb1if object_id('tb2') Is Not Null
Drop Table tb2create table tb1(a varchar(50),b varchar(50),c int,d datetime)
insert into tb1
select '','a001',-100,'2011-3-1' union all
select '','a001',-50, '2011-4-1' union all
select '','a002',-40, '2011-5-1' union all
select '','a002',-40, '2011-6-1' union all
select '','a002',-80, '2011-6-1' 
create table tb2(a varchar(50),b varchar(50),c int,d datetime)
insert into tb2
select 's0','a001',30,'2011-2-25' union all
select 's1','a001',40, '2011-2-25' union all
select 's2','a001',50, '2011-2-25' union all
select 's3','a001',80, '2011-2-25' union all
select 's4','a002',60, '2011-10-30' union all
select 's5','a002',40, '2011-5-30' union all
select 's6','a002',50, '2011-4-20' union all
select 's7','a002',30, '2011-4-25' union all
select 's8','a002',30, '2011-4-10' 
GO
;With CTE_base As
(
Select a.b,a.c,a.d,b.c As b_c,b.a As b_a,row_number() Over(partition By a.b,a.c,a.d Order By b.a) As row
From tb1 As a
Inner Join tb2 As b on b.b=a.b
And a.d>=b.d

),CTE_Add As
(
Select a.b,a.c,a.d,a.b_c,a.b_a,a.row,Convert(int,a.b_c) As sum_add
From CTE_base As a
Where row=1
Union All
Select a.b,a.c,a.d,a.b_c,a.b_a,a.row,Convert(int,b.sum_add+a.b_c) As sum_add
From CTE_base As a
Inner Join CTE_Add As b On b.b=a.b 
And b.c=a.c 
And b.d=a.d 
Where b.row+1=a.row

)
Update a 
Set a.a=b.a
From tb1 As a
Outer Apply(Select Stuff((Select ','+x.b_a
From CTE_Add x
Where x.b=a.b 
And x.c=a.c 
And x.d=a.d 
And (a.c+x.sum_add<=0 or
 (a.c+x.sum_add>0 And a.c+x.sum_add<x.b_c) )
for xml Path('') 
),1,1,'') As a  
) As bSelect * From tb1
/*
a b c d
---------------------------------------------
s0,s1,s2 a001 -100 2011-03-01 00:00:00.000
s0,s1 a001 -50 2011-04-01 00:00:00.000
s6 a002 -40 2011-05-01 00:00:00.000
s5 a002 -40 2011-06-01 00:00:00.000
s5,s6 a002 -80 2011-06-01 00:00:00.000*/

Answer 10

能不能帮我转为sql2000啊？

Answer 11

create table tb1(a varchar(50),b varchar(50),c int,d datetime)
insert into tb1
select '','a001',-100,'2011-3-1' union all
select '','a001',-50, '2011-4-1' union all
select '','a002',-40, '2011-5-1' union all
select '','a002',-40, '2011-6-1' union all
select '','a002',-80, '2011-6-1' create table tb2(a varchar(50),b varchar(50),c int,d datetime)
insert into tb2
select 's0','a001',30,'2011-2-25' union all
select 's1','a001',40, '2011-2-25' union all
select 's2','a001',50, '2011-2-25' union all
select 's3','a001',80, '2011-2-25' union all
select 's4','a002',60, '2011-10-30' union all
select 's5','a002',40, '2011-5-30' union all
select 's6','a002',50, '2011-4-20' union all
select 's7','a002',30, '2011-4-25' union all
select 's8','a002',30, '2011-4-10' select tb1.b,tb1.c,tb1.d,tb2.a into tb3
from  tb1 left join tb2
on tb1.b=tb2.b and tb2.d<=tb1.d
where 
not exists
(select 1 from tb2 as t1 
where t1.b=tb2.b and t1.a=tb2.a and t1.d<=tb1.d
and (select sum(c) from tb2 as t2  where t2.b=t1.b and t2.d<=tb1.d and t2.a<=t1.a)+tb1.c>=0
and (select sum(c) from tb2 as t2  where t2.b=t1.b and t2.d<=tb1.d and t2.a<t1.a)+tb1.c>=0
)goCREATE FUNCTION dbo.f_str(@b varchar(10),@c int,@d datetime)
RETURNS varchar(100)
AS
BEGIN
  DECLARE @r varchar(8000)
  SET @r = ''
  SELECT @r = @r + ',' + a FROM tb3 WHERE b=@b and c=@c and d=@d
  RETURN STUFF(@r, 1, 1, '')
ENDgoupdate tb1
set tb1.a=tb.a
from (select b,c,d,dbo.f_str(b,c,d) as a from tb3 group by b,c,d) tb
where tb.b=tb1.b and tb.c=tb1.c and tb.d=tb1.d
select * from tb1drop FUNCTION dbo.f_strdrop table tb1,tb2,tb3/*
a           b        c                   d    
--------------------------------------------------------
s0,s1,s2    a001    -100        2011-03-01 00:00:00.000
s0,s1       a001    -50         2011-04-01 00:00:00.000
s6          a002    -40         2011-05-01 00:00:00.000
s5          a002    -40         2011-06-01 00:00:00.000
s5,s6       a002    -80         2011-06-01 00:00:00.000（所影响的行数为 5 行）
(5 行受影响)
*/