日期:2014-05-17  浏览次数:20464 次

求两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

--1
if object_id('[a]') is not null drop table [a]
go
create table [a]([code] varchar(8),[value] int,[lev] int)
insert [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
go

--更新
update t
set value=(select sum(value) from a where code like t.code+'%')
from a t
where value is null
go

--结果
select * from a
/**
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

(8 行受影响)
**/


--2
if object_id('[b]') is not null drop table [b]
go
create table [b]([code] varchar(2),[val1] int,[val2] int,[val3] int)
insert [b]
select '01',10,20,30 union all
select '02',11,12,13 union all
select '03',101,88,0
go

--查询
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) t
exec ('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          101
val2 20          12          88
val3 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