一个sql语句，效率问题。欢迎大神指教
我在项目中遇到遇到一个问题,下面是这个问题的抽象：
table A
col1 col2
1 1
1 2
1 3
1 4
2 1
3 2
3 3
table B
col1 col2
10000
2 0000
3 0000
我现在想写一个存储过程来更新表B的第二个字段，达到下面的效果：
table B
col1 col2
11111
2 1000
3 0110
我现在是通过游标枚举B表中col1，再去A表中统计信息，统计好之后在更新A表，但是这样很慢，大神有什么高效的方法吗？就60分了，，，，，， 数据库 SQL 存储过程 游标 查询
[解决办法]
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]
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
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
