27,579
社区成员
发帖
与我相关
我的任务
分享
update 表2 set sSQL=replace(sSQL,'$1$',a.val)
from 表1 a where 表2.SQLID=a.SQLID and a.id=1
update 表2 set sSQL=replace(sSQL,'$2$',a.val)
from 表1 a where 表2.SQLID=a.SQLID and a.id=2
declare @t1 table(SQLID int, ID int, VAL varchar(20))
declare @t2 table(SQLID int, sSQL varchar(2000))
insert into @t1
select 1, 1, '1' union all
select 1, 2, 'ffwe' union all
select 2, 1, '1' union all
select 2, 2, 'Red'
insert into @t2
select 1, 'select count(*) from venmasm where ven_id<>$1$ and ven_code=rtrim(''$2$'')' union all
select 2, 'select count(*) from colorflm where color_id<>$1$ and color_code=rtrim(''$2$'')'
select REPLACE(REPLACE(A.sSQL, '$1$', B.ID1), '$2$', B.ID2) SQL from @t2 A left join (
select SQLID, max(case when ID=1 then VAL else '' end) ID1
, max(case when ID=2 then VAL else '' end) ID2
from @t1 group by SQLID) B on A.SQLID=B.SQLID
/*
SQL
----------------------------------------------------------------------------
select count(*) from venmasm where ven_id<>1 and ven_code=rtrim('ffwe')
select count(*) from colorflm where color_id<>1 and color_code=rtrim('Red')
*/
select count(*) from venmasm a where ven_id<>(select top 1 val from 表1 where sqlid=a.sqlid and id=1) and ven_code=rtrim('$2$')
select count(*) from colorflm a where color_id<>(select top 1 val from 表1 where sqlid=a.sqlid and id=2) and color_code=rtrim('$2$')
select count(*) from venmasm a where ven_id<>(select top 1 val from 表1 where sqlid=a.sqlid and id=1) and ven_code=rtrim('$2$')
select count(*) from colorflm where color_id<>(select top 1 val from 表1 where sqlid=a.sqlid and id=2) and color_code=rtrim('$2$')