一个纵改横 关联表执行效率问题

Question

表 TAB1
 ID   NAME
 1     AA
 2     BB表TAB2 为  TAB1表记录的扩展属性
 KZSXM    KZNR   TAB1ID
  SIZE      5     1
  CL        红    1
  cost     100    1
  CZ        铜    1
  SIZE      8     2
  CL        绿    2
  cost     200    2
  CZ        铜    2结果
ID  NAME  SIZE  CL  COST  CZ 
1    AA     5   红  100   铜
2    BB     8   绿  200   铜Select 
  e.id
  e.name
  a.kznr AS size,
  b.kznr as cl,
  C.kznr AS cost,
  D.kznr as cz,
   from
  (select kznr from tab2 where KZSXM='SIZE') a
 join
  select kznr from tab2 where KZSXM='cl') b
on a.tab1id=b.tabiid
join
  (select kznr from tab2 where KZSXM='cost') c
on a.tab1id=c.tabiid
join
  (select kznr from tab2 where KZSXM='cz') d
On a.fforeignid=d.fforeignid
join tab1 e
on a.tab1id=e.id  
实现这个结果怎么写更优化。

Answer 1

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-16 15:33:16
-------------------------------------
 
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID int,NAME varchar(2))
INSERT INTO @tb1
SELECT 1,'AA' UNION ALL
SELECT 2,'BB'
 
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (KZSXM varchar(4),KZNR varchar(3),TAB1ID int)
INSERT INTO @tb2
SELECT 'SIZE','5',1 UNION ALL
SELECT 'CL','红',1 UNION ALL
SELECT 'cost','100',1 UNION ALL
SELECT 'CZ','铜',1 UNION ALL
SELECT 'SIZE','8',2 UNION ALL
SELECT 'CL','绿',2 UNION ALL
SELECT 'cost','200',2 UNION ALL
SELECT 'CZ','铜',2--SQL查询如下:SELECT A.ID,A.NAME,
    MAX(CASE WHEN B.KZSXM = 'SIZE' THEN KZNR ELSE '' END) AS SIZE,
    MAX(CASE WHEN B.KZSXM = 'CL' THEN KZNR ELSE '' END) AS CL,
    MAX(CASE WHEN B.KZSXM = 'cost' THEN KZNR ELSE '' END) AS cost
FROM @tb1 AS A
    JOIN @tb2 AS B
ON A.ID = B.TAB1ID
GROUP BY A.ID,A.NAME/*
ID          NAME SIZE CL   cost
----------- ---- ---- ---- ----
1           AA   5    红    100
2           BB   8    绿    200(2 行受影响)*/

Answer 2

行列转换 见精华帖子
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?76708

Answer 3

declare @t table([ID] int,[NAME] varchar(2))
insert @t
select 1,'AA' union all
select 2,'BB'
 declare @tb table([KZSXM] varchar(4),[KZNR] varchar(3),[TAB1ID] int)
insert @tb
select 'SIZE','5',1 union all
select 'CL','红',1 union all
select 'cost','100',1 union all
select 'CZ','铜',1 union all
select 'SIZE','8',2 union all
select 'CL','绿',2 union all
select 'cost','200',2 union all
select 'CZ','铜',2---sql2005
select ID,  NAME , SIZE,  CL , COST , CZ  from 
(
select * from @tb
pivot
(
  max(kznr) for [KZSXM] in(SIZE,CL,cost,CZ)
) p 
) tmp join @T t on tmp.[TAB1ID]=t.id/*
ID          NAME SIZE CL   COST CZ
----------- ---- ---- ---- ---- ----
1           AA   5    红    100  铜
2           BB   8    绿    200  铜(2 行受影响)
*/

Answer 4

create table tb1 (ID int,NAME varchar(2))
INSERT INTO tb1
SELECT 1,'AA' UNION ALL
SELECT 2,'BB'
 
create table tb2 (KZSXM varchar(4),KZNR varchar(3),TAB1ID int)
INSERT INTO tb2
SELECT 'SIZE','5',1 UNION ALL
SELECT 'CL','红',1 UNION ALL
SELECT 'cost','100',1 UNION ALL
SELECT 'CZ','铜',1 UNION ALL
SELECT 'SIZE','8',2 UNION ALL
SELECT 'CL','绿',2 UNION ALL
SELECT 'cost','200',2 UNION ALL
SELECT 'CZ','铜',2--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select tb1.* '
select @sql = @sql + ' , max(case KZSXM when ''' + KZSXM + ''' then KZNR else '''' end) [' + KZSXM + ']'
from (select distinct KZSXM from tb2) as a
set @sql = @sql + ' from tb2,tb1 where tb2.TAB1ID = tb1.id group by tb1.id,tb1.name order by tb1.id'
exec(@sql) drop table tb1 , tb2/*
ID          NAME CL   cost CZ   SIZE 
----------- ---- ---- ---- ---- ---- 
1           AA   红    100  铜    5
2           BB   绿    200  铜    8*/

Answer 5

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-16 15:51:29.233●●●●●
　★★★★★soft_wsx★★★★★
*/if object_ID('TB1') IS NOT NULL DROP TABLE TB1
go
  create TABLE tb1(ID int,NAME varchar(2))
go
insert tb1
SELECT 1,'AA' UNION ALL
SELECT 2,'BB'
go
if object_ID('TB2') IS NOT NULL DROP TABLE TB2
go
  create TABLE TB2(KZSXM varchar(4),KZNR varchar(3),TAB1ID int)
go
INSERT INTO TB2
SELECT 'SIZE','5',1 UNION ALL
SELECT 'CL','红',1 UNION ALL
SELECT 'cost','100',1 UNION ALL
SELECT 'CZ','铜',1 UNION ALL
SELECT 'SIZE','8',2 UNION ALL
SELECT 'CL','绿',2 UNION ALL
SELECT 'cost','200',2 UNION ALL
SELECT 'CZ','铜',2
declare @sql nvarchar(4000)
SET @sql=N'select a.*'   --初始化变量必须
select @sql=@sql+N','+
                  QUOTENAME(KZSXM)+
                    N'=max(
                            case when [KZSXM]='+quotename(KZSXM,N'''')
                            +N' then KZNR else null end)'
                    from tb2 group by KZSXM 
                    order by KZSXM desc
       print @sql                      
   
exec(@sql+N' from tb1 a,tb2 b where a.id=b.TAB1ID group by a.ID,a.NAME ')
                 
/*
ID NAME SIZE CZ cost CL
1 AA 5 铜 100 红
2 BB 8 铜 200 绿
*/
select a.*,[SIZE]=max(
                            case when [KZSXM]='SIZE' then KZNR else null end),[CZ]=max(
                            case when [KZSXM]='CZ' then KZNR else null end),[cost]=max(
                            case when [KZSXM]='cost' then KZNR else null end),[CL]=max(
                            case when [KZSXM]='CL' then KZNR else null end)
  from tb1 a,tb2 b where a.id=b.TAB1ID group by a.ID,a.NAME