一个sql语句，效率问题。欢迎大神指教

[iLoveHeineken]

我在项目中遇到遇到一个问题,下面是这个问题的抽象：
table A
col1 col2
1 1
1 2
1 3
1 4
2 1
3 2
3 3


table B
col1 col2
1 0000
2 0000
3 0000
我现在想写一个存储过程来更新表B的第二个字段，达到下面的效果：
table B
col1 col2
1 1111
2 1000
3 0110

我现在是通过游标枚举B表中col1，再去A表中统计信息，统计好之后在更新A表，但是这样很慢，大神有什么高效的方法吗？就60分了，，，，，，

[魔者侠心之明星级程序员]

TRY

update  B
SET B.[col2]= A.[col2]
FROM  [dbo].[tableB] B INNER JOIN 
(
SELECT [col1],[col2]=right('0000' + convert(varchar,sum(POWER (10,4-(col2)))),4)  
from [dbo].[tableA]
group by [col1]
) A ON B.[col1]=A.[col1]
 

[哥眼神纯洁不]

不更新，直接用语句把a表转换...

with tb as (
select 1 a ,1 b union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,1 union all
select 3,2 union all
select 3,3
),
tc as( 
select distinct * from (select a  from tb)a,(select b from tb)b)
,td as(
select tc.a,(case when tb.b is null then '0' else '1' end)b 
from tc left join tb on tc.a=tb.a and tc.b=tb.b)
select distinct a,b=(select ''+b from td where a.a=a for xml path('')) from td a

[iLoveHeineken]

引用 1 楼 SQL_Beginner 的回复:

TRY SQL code?123456789update BSET B.[col2]= A.[col2]FROM [dbo].[tableB] B INNER JOIN (SELECT [col1],[col2]=right('0000' + convert(varchar,sum(POWER (10,4-(col2)))),4) from [dbo].[tabl……

我可能没有说明白我的意思，我的意思是把如果A表中有1、1，就把B表中的col2更新为1000，而不是0100、0010或者其他

[iLoveHeineken]

引用 2 楼 sc273607742 的回复:

不更新，直接用语句把a表转换... SQL code?123456789101112131415with tb as (select 1 a ,1 b union allselect 1,2 union allselect 1,3 union allselect 1,4 union allselect 2,1 union allselect 3,2 union alls……

我想通过A表直接更新B表。能再指点下？

[弘恩]

我的方法怎么和楼上，同出一辙呢！？

WITH    CTE
          AS ( SELECT   1 AS ctecol1 ,1 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   1 AS ctecol1 ,2 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   1 AS ctecol1 ,3 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   1 AS ctecol1 ,4 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   2 AS ctecol1 ,1 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   2 AS ctecol1 ,2 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   2 AS ctecol1 ,3 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   2 AS ctecol1 ,4 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   3 AS ctecol1 ,1 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   3 AS ctecol1 ,2 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   3 AS ctecol1 ,3 AS ctecol2 ,'0' AS ctecol3
               UNION ALL
               SELECT   3 AS ctecol1 ,4 AS ctecol2 ,'0' AS ctecol3
             ),
 cte2 AS (  SELECT  a.ctecol1,
            CASE WHEN t.col2 > 0 THEN '1' ELSE '0' END col 
			FROM    CTE a
            LEFT JOIN A_test t ON a.ctecol1 = t.col1 
								 AND a.ctecol2 = t.col2 )
								 
  SELECT ctecol1,(SELECT col +'' FROM cte2 a WHERE a.ctecol1 = t.ctecol1   FOR XML PATH ('') ) AS target_value 
   FROM cte2 t  
GROUP BY ctecol1

[弘恩]

关键你要构造一下值原型表，这个至少要比游标快多了

[iLoveHeineken]

引用 8 楼 sc273607742 的回复:

引用 6 楼 weepingisgood 的回复:引用 2 楼 sc273607742 的回复: 不更新，直接用语句把a表转换... SQL code?123456789101112131415with tb as (select 1 a ,1 b union allselect 1,2 union allselect 1,3 union allselect 1,4 ……

昨天自己埋头研究体会出来了，不过我自己抽象问题的时候误导你们了，其实是这样的： table A col1 col2 1 1 1 2 1 3 1 4 2 1 3 2 3 9 table B col1 col2 1 0000000000 2 0000000000 3 0000000000 我现在想写一个存储过程来更新表B的第二个字段，达到下面的效果： table B col1 col2 1 1100011000 2 1100000000 3 0000011000

[魔者侠心之明星级程序员]

引用 5 楼 weepingisgood 的回复:

引用 1 楼 SQL_Beginner 的回复: TRY SQL code?123456789update BSET B.[col2]= A.[col2]FROM [dbo].[tableB] B INNER JOIN (SELECT [col1],[col2]=right('0000' + convert(varchar,sum(POWER (10,4-(col2)))),4) fro……

依旧不明白了，我试了一下其他两人给出的结果，跟我是一样的。

[哥眼神纯洁不]

引用 6 楼 weepingisgood 的回复:

引用 2 楼 sc273607742 的回复: 不更新，直接用语句把a表转换... SQL code?123456789101112131415with tb as (select 1 a ,1 b union allselect 1,2 union allselect 1,3 union allselect 1,4 union allselect 2,1 union allselect 3,……

直接update就行啊..

with tb as (
select 1 a ,1 b union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,1 union all
select 3,2 union all
select 3,3
),
--上面是建标语句，为了测试用的，你要用从下面开始用
tc as( 
select distinct * from (select a  from tb)a,(select b from tb)b)
,td as(
select tc.a,(case when tb.b is null then '0' else '1' end)b 
from tc left join tb on tc.a=tb.a and tc.b=tb.b)
,te as(
select distinct a,b=(select ''+b from td where a.a=a for xml path('')) from td a)
--下面是更新语句
update b
set col2=te.b from te where te.a=b.col1