问题描述

SQL2005数据处理的问题

select * INTO #a
from (SELECT ROW_NUMBER() OVER (ORDER BY dy) xx,aid,company, name, areaID, dy, bz, rem, groupname, price425, price325
FROM Attribute
where compid=101 and areaid=1) a
select * from #a

select * into #b
from (SELECT ROW_NUMBER() OVER (ORDER BY po) xx,
(case when po-poold
when po-poold=0 then 0
when po-poold>0 then 1 end) as t, dbo.Groups.dnam, pc,po,pcold, poold, dbo.T_PRICE.rem
FROM dbo.T_PRICE INNER JOIN
dbo.Groups ON dbo.T_PRICE.groupID = dbo.Groups.groupID
where compid=101 and areaid=1) b
select * from #b

如上生成两个临时表，要将#b中的值更新按照一定次序到#a中，如何实现比较方便？我采用如下方式

update #a
set groupname=#b.dnam,price425=#b.poold,price325=#b.pcold,bz=#b.t
from #a,#b
where #b.xx=#a.xx

update attribute
set groupname=#a.groupname,price425=#a.price425,price325=#a.price325,bz=#a.bz
from attribute,#a
where attribute.aid=#a.aid

这种方式如果where compid=101 and areaid=1是固定的可以实现，但是如果是变化的要如何处理呢？compid可以是101、101....areaid可以是1、2...,如果一一罗列就太麻烦啦。求教各位大侠，请指点迷津！万分感谢。。