纵向数据，横向显示,急
表1
____________________________________________________________________
| plan(单号) | genre(类型) | model(型号) | total(合计) |
| | | | |
| 00100 | ic | ac105 | 100 |
| 00100 | pcb | sa200 | 101 |
| 00101 | ic | acd312 | 500 |
| 00101 | pcb | ns205 | 503 |
| | | | |
--------------------------------
表2
______________________________________________________________________
| plan(单号) | ic_model | ic_total | pcb_model | pcb_total |
| | | | | |
| 00100 | ac105 | 100 | sa200 | 101 |
| 00100 | acd312 | 500 | ns205 | 503 |
----------------------------------
表3
_________________________________________________
| plan(单号) | model | total |
| | | |
| 00100 | ac105(as200) | 50 |
| 00100 | acd312(ns205) | 500 |
-------------------------------------------------
问题1:请问如何将第一个表输入成第二个表显示?
问题2:请问如何将第二个显示的表和第三个表内部连接?
[解决办法]
问题1:请问如何将第一个表输入成第二个表显示?
表2
______________________________________________________________________
| plan(单号) | ic_model | ic_total | pcb_model | pcb_total |
| | | | | |
| 00100 | ac105 | 100 | sa200 | 101 |
| 00100 | acd312 | 500 | ns205 | 503 |
----------------------------------
declare @a table( plans int,genre varchar(20),model varchar(20),total int)
insert @a
select 100, 'ic ', 'ac105 ',100
union all
select 100, 'pcb ', 'sa200 ',101
union all
select 101, 'ic ', 'acd312 ',500
union all
select 101, 'pcb ', 'ns205 ',503
select a.plans,a.model ic_model,a.total ic_total,b.model pcb_model,b.total pcb_total from @a a,@a b where a.plans=b.plans and a.genre <> b.genre and a.total <b.total
（所影响的行数为 4 行）
plans ic_model ic_total pcb_model pcb_total
----------- -------------------- ----------- -------------------- -----------
100 ac105 100 sa200 101
101 acd312 500 ns205 503
（所影响的行数为 2 行）
问题2:请问如何将第二个显示的表和第三个表内部连接?
select * from 表3 c,(
select a.plans,a.model,a.total,b.model,b.total from @a a,@a b where a.plans=b.plans and a.genre <> b.genre and a.total <b.total
) d where c.plan=d.plan
[解决办法]
create table #tt(
[plan] varchar(100),
genre varchar(100),
model varchar(100),
total int
)
insert into #tt select '00100 ', 'ic ', 'ac105 ',100
union all select '00100 ', 'pcb ', 'sa200 ',101
union all select '00101 ', 'ic ', 'acd312 ',500
union all select '00101 ', 'pcb ', 'ns205 ',503
/*静态
表1-2*/
select [plan],
ic_model=max(case when genre= 'ic ' then model end),
ic_total=max(case when genre= 'ic ' then total end),
pcb_model=max(case when genre= 'pcb ' then model end),
pcb_total=max(case when genre= 'pcb ' then total end)
from #tt group by [plan]
/*表关联*/
select a.*
from 表3 a inner join
(select [plan],
ic_model=max(case when genre= 'ic ' then model end),
ic_total=max(case when genre= 'ic ' then total end),
pcb_model=max(case when genre= 'pcb ' then model end),
pcb_total=max(case when genre= 'pcb ' then total end)
from #tt group by [plan]) b on a.[plan]=b.[plan]
/*动态
表1-2*/
declare @sql varchar(1000)
select @sql= ' '
select @sql=@sql+ ', '+genre+ '_model=max(case when genre= ' ' '+genre+ ' ' ' then model end), '
+genre+ '_total=max(case when genre= ' ' '+genre+ ' ' ' then total end) '
from #tt group by genre
select @sql= 'select [plan] '+@sql+ ' from #tt group by [plan] '
exec(@sql)
/*表关联*/
declare @sql varchar(1000)
select @sql= ' '
select @sql=@sql+ ', '+genre+ '_model=max(case when genre= ' ' '+genre+ ' ' ' then model end), '
+genre+ '_total=max(case when genre= ' ' '+genre+ ' ' ' then total end) '
from #tt group by genre
select @sql= 'select [plan] '+@sql+ ' from #tt group by [plan] '
select @sql= 'select a.* from 表3 a inner join ( '+@sql+ ') b on a.[plan]=b.[plan] '
print(@sql)
