求两SQL语句
1.表a结构如下：
code value lev
zb null 1
zb.sz null 2
zb.sz.01 11 3
zb.sz.02 10 3
zb.sz.03 9 3
zb.sy null 2
zb.sy.01 5 3
zb.sy.02 4 3
需要更新父级值，得到如下结果
code value lev
zb 39 1
zb.sz 30 2
zb.sz.01 11 3
zb.sz.02 10 3
zb.sz.03 9 3
zb.sy 9 2
zb.sy.01 5 3
zb.sy.02 4 3
2.表b结构如下
code val1 val2 val3
01 10 20 30
02 11 12 13
03 101 88 0
需要行转列如下结果
code 01 02 03
val1 10 11 101
val2 20 12 88
val3 30 13 0
分数不多，求高人
[解决办法]
行转列看这里：http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
[解决办法]
- SQL code
--1if object_id('[a]') is not null drop table [a]gocreate table [a]([code] varchar(8),[value] int,[lev] int)insert [a]select 'zb',null,1 union allselect 'zb.sz',null,2 union allselect 'zb.sz.01',11,3 union allselect 'zb.sz.02',10,3 union allselect 'zb.sz.03',9,3 union allselect 'zb.sy',null,2 union allselect 'zb.sy.01',5,3 union allselect 'zb.sy.02',4,3go--更新update tset value=(select sum(value) from a where code like t.code+'%')from a twhere value is nullgo--结果select * from a/**code value lev-------- ----------- -----------zb 39 1zb.sz 30 2zb.sz.01 11 3zb.sz.02 10 3zb.sz.03 9 3zb.sy 9 2zb.sy.01 5 3zb.sy.02 4 3(8 行受影响)**/--2if object_id('[b]') is not null drop table [b]gocreate table [b]([code] varchar(2),[val1] int,[val2] int,[val3] int)insert [b]select '01',10,20,30 union allselect '02',11,12,13 union allselect '03',101,88,0go--查询declare @sql varchar(8000)select @sql=isnull(@sql+',','') +'max(case when code='''+code+''' then val else 0 end) as ['+code+']'from(select distinct code from b) texec ('select c as code,'+@sql+'from (select code,val1 as val,''val1'' as c from b union all select code,val2,''val2'' from b union all select code,val3,''val3'' from b ) t group by c')--结果/**code 01 02 03---- ----------- ----------- -----------val1 10 11 101val2 20 12 88val3 30 13 0(3 行受影响)**/
[解决办法]
/*
---递归更新父级节点
DECLARE @a TABLE
( code varchar(10), value int, lev int)
INSERT INTO @a
SELECT 'zb', null , 1
UNION ALL
SELECT 'zb.sz', null, 2
UNION ALL
SELECT 'zb.sz.01', 11, 3
UNION ALL
SELECT 'zb.sz.02', 10, 3
UNION ALL
SELECT 'zb.sz.03' ,9, 3
UNION ALL
SELECT 'zb.sy' ,null, 2
UNION ALL
SELECT 'zb.sy.01', 5, 3
UNION ALL
SELECT 'zb.sy.02' ,4, 3 -- 准备测试数据
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
;WITH c1 AS --将源表中的父节点展示出来，parent列；（包括根节点）
(
SELECT a.code parent, b.code, b.value, b.lev
FROM @a a
JOIN @a b ON CHARINDEX(a.Code, b.code) <> 0 AND b.lev - a.lev = 1
UNION ALL
SELECT NULL, a.code, a.value, a.lev
FROM @a a
WHERE a.lev = 1
)
SELECT *
INTO #temp
FROM c1
DECLARE @level int = ( SELECT MAX(lev) FROM #temp ) -- 找出叶子节点所在的层级
WHILE(@level <> (SELECT MIN(lev) FROM #temp))
BEGIN
UPDATE t---更新父表t中的内容，t作为父表，c作为子表
SET t.value = c.SumValue
FROM #temp t
INNER JOIN
(
SELECT parent, SUM(value) SumValue
FROM #temp
WHERE lev = @level
GROUP BY parent
) c ON t.code = c.parent
SET @level = @level - 1
END
SELECT *
FROM #temp
ORDER BY lev
**/
----测试结果
parent code value lev
---------- ---------- ----------- -----------
NULL zb 39 1
zb zb.sz 30 2
zb zb.sy 9 2
zb.sz zb.sz.01 11 3
zb.sz zb.sz.02 10 3
zb.sz zb.sz.03 9 3
zb.sy zb.sy.01 5 3
zb.sy zb.sy.02 4 3
(8 row(s) affected)
行列互换写不下去了，想睡觉了……