求SQL:根据T1表的条件从表T2取数据并更新字段数值.

Question

--两个表结构
create table T1(gongshi varchar(250),shuzhi numeric(18, 2))
create table T2(vid varchar(50),shuzhi numeric(18, 2))--测试数据
insert into T1 values('=code(101)',0)
insert into T1 values('=code(102)',0)
insert into T1 values('=code(101)+code(102)',0)insert into T2 values('101',1000)
insert into T2 values('102',500)--要求实现（根据表2的数据update表T1的值，如下：）
   gongshi                         shuzhi               =code(101)                   1000
  =code(102)                    500
  =code(101)+code(102)           1500

Answer 1

  select M.gongshi,sum(N.shuzhi)
  from #T1 M
  LEFT JOIN #T2 N
  ON CHARINDEX(N.VID,M.gongshi)>0
  group by m.gongshi
  order by  M.gongshi
  gongshi                       shuzhi
------------ ---------------------------------------
=code(101)                      1000.00
=code(101)+code(102)            1500.00
=code(102)                      500.00(3 row(s) affected)

Answer 2

go
create table T1(gongshi varchar(250),shuzhi numeric(18, 2))
create table T2(vid varchar(50),shuzhi numeric(18, 2))
--测试数据
insert into T1 values('=code(101)',0)
insert into T1 values('=code(102)',0)
insert into T1 values('=code(101)+code(102)',0)
insert into T2 values('101',1000)
insert into T2 values('102',500)
select a.gongshi,
       sum(b.shuzhi)shuzhi
from t1 a left join t2 b
on a.gongshi like '%('+b.vid+')%' 
group by a.gongshi
order by shuzhi
drop table t1,t2
/*
gongshi                                                                                                                                                                                                                                                    shuzhi
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
=code(102)                                                                                                                                                                                                                                                 500.00
=code(101)                                                                                                                                                                                                                                                 1000.00
=code(101)+code(102)                                                                                                                                                                                                                                       1500.00(3 行受影响)*/

Answer 3

  update T
  set T.shuzhi=M.shuzhi
  from #T1 T
  LEFT JOIN 
  (
  select M.gongshi,sum(N.shuzhi)shuzhi
  from #T1 M
  LEFT JOIN #T2 N
  ON CHARINDEX(N.VID,M.gongshi)>0
  group by m.gongshi
  )M
  ON T.gongshi=M.gongshi-------
=code(101)         1000.00
=code(102)         500.00
=code(101)+code(102) 1500.00

Answer 4

go
create table T1(gongshi varchar(250),shuzhi numeric(18, 2))
create table T2(vid varchar(50),shuzhi numeric(18, 2))
--测试数据
insert into T1 values('=code(101)',0)
insert into T1 values('=code(102)',0)
insert into T1 values('=code(101)+code(102)',0)
insert into T2 values('101',1000)
insert into T2 values('102',500)update t1 set shuzhi=isnull((select sum(shuzhi)
                     from t2
                     where t1.gongshi like '%('+t2.vid+')%'),shuzhi)
select * from t1
drop table t1,t2

Answer 5

不好意思要因实际情况有相减的，加一个补充说明：insert into T2 values('103',100)insert into T1 values('=code(101)-code(102)-code(103)',0)需要最后结果：gongshi                             shuzhi  =code(101)-code(102)-code(103）    400.00

Answer 6

参考
http://blog.csdn.net/wufeng4552/archive/2009/11/25/4868138.aspx

Answer 7

insert into T1 values('=code(101)-code(102)-code(103)',0)实际就是这么存储的吗？这一列值就是 =code(101)-code(102)-code(103) 这个？

Answer 8

回复:SQL2088这一列是公式. =code(101)-code(102)-code(103)没有错.

Answer 9

create table T1(gongshi varchar(250),shuzhi numeric(18, 2))
create table T2(vid varchar(50),shuzhi numeric(18, 2))--测试数据
insert into T1 values('=code(101)',0)
insert into T1 values('=code(102)',0)
insert into T1 values('=code(101)+code(102)',0)insert into T2 values('101',1000)
insert into T2 values('102',500)
create function [dbo].[code](
@code int
)
 returns numeric(18, 2)
as 
begindeclare @result numeric(18, 2)select @result = (select top 1  shuzhi from t2 where vid = 
cast(@code  as varchar(8)))
return  @result
enddeclare @sql varchar(1000)
update t1 set gongshi = replace(gongshi,'code','dbo.code')
set @sql = ''
select  @sql = @sql +  '    update t1 set shuzhi  ' + gongshi 
+ ' where gongshi = ''' + gongshi + ''''
        from t1select @sqlexec(@sql)
update t1 set gongshi = replace(gongshi,'dbo.code','code')
select * from t1