关于子查询的问题,高手进.

[zhangyang555]

Table1
ID Name WageID .....
1 zy 1 .....
2 zc 2 .....

Table1有一个子表Table2,通过WageID关联
ID WageID Name Wage
1 1 补助 100
2 1 保险 500
3 1 奖金 200
4 2 费用1 50
5 2 费用2 300


现在需要得出这样的结果:
Table1
ID Name WageID WageName1 Wage1 WageName2 Wage2 WageName3 Wage3
1 zy 1 补助 100 保险 500 奖金 200
2 zc 2 费用1 50 费用2 300
.......

[-狙击手-]

行列转换，精华里多呀，找找

[黄_瓜]

[Quote=引用 1 楼 happyflystone 的回复:]
行列转换，精华里多呀，找找
[/Quote]
太多了 搜“行列转换”

[feixianxxx]

--静态：
select tb1.id,
tb1.name,
tb1.wageid,
wagename1=MAX(case when rn=1 then tb2.name end),
wage1=MAX(case when rn=1 then tb2.wage end),
wagename2=MAX(case when rn=2 then tb2.name end),
wage2=MAX(case when rn=2 then tb2.wage end),
wagename3=MAX(case when rn=3 then tb2.name end),
wage3=MAX(case when rn=3 then tb2.wage end)
from tb1 join 
(select *,rn=ROW_NUMBER()over(PARTITION by wageid order by id) from tb2 ) tb2 
on tb1.wageid=tb2.wageid
group by tb1.id,
tb1.name,
tb1.wageid

[dawugui]

--sql 2000用动态SQL+行列转换.

create table tb1(ID int,Name varchar(10), WageID int)
insert into tb1 values(1, 'zy', 1) 
insert into tb1 values(2, 'zc', 2)

create table tb2(ID int,WageID int,Name varchar(10),Wage int)
insert into tb2 values(1 ,1 ,'补助' ,100)
insert into tb2 values(2 ,1 ,'保险' ,500)
insert into tb2 values(3 ,1 ,'奖金' ,200)
insert into tb2 values(4 ,2 ,'费用1', 50)
insert into tb2 values(5 ,2 ,'费用2', 300)
go


declare @sql varchar(8000)
set @sql = 'select m.ID , m.Name , m.WageID '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then n.Name else '''' end) [WageName' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(n.Wage as varchar) else '''' end) [Wage' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb2 where WageID = t.WageID and id < t.id) + 1 from tb2 t) k) as a
set @sql = @sql + ' from tb1 m , (select t.* , px = (select count(1) from tb2 where WageID = t.WageID and id < t.id) + 1 from tb2 t) n where m.WageID = n.WageID group by m.ID , m.Name , m.WageID'
exec(@sql) 


drop table tb1 , tb2

/*

ID          Name       WageID      WageName1  Wage1                          WageName2  Wage2                          WageName3  Wage3                          
----------- ---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 
1           zy         1           补助         100                            保险         500                            奖金         200
2           zc         2           费用1        50                             费用2        300                                       

*/

[zhangyang555]

子表中的项目是不固定的.有可能是3个,有可能是5个.

[dawugui]

--sql 2005用动态SQL+row_number+行列转换.

create table tb1(ID int,Name nvarchar(10), WageID int)
insert into tb1 values(1, N'zy', 1) 
insert into tb1 values(2, N'zc', 2)

create table tb2(ID int,WageID int,Name nvarchar(10),Wage int)
insert into tb2 values(1 ,1 ,N'补助' ,100)
insert into tb2 values(2 ,1 ,N'保险' ,500)
insert into tb2 values(3 ,1 ,N'奖金' ,200)
insert into tb2 values(4 ,2 ,N'费用1', 50)
insert into tb2 values(5 ,2 ,N'费用2', 300)
go

declare @sql varchar(8000)
set @sql = 'select m.ID , m.Name , m.WageID '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then n.Name else '''' end) [WageName' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(n.Wage as varchar) else '''' end) [Wage' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = row_number() over(partition by WageID order by id) from tb2 t) k) as a
set @sql = @sql + ' from tb1 m , (select t.* , px = row_number() over(partition by WageID order by id) from tb2 t) n where m.WageID = n.WageID group by m.ID , m.Name , m.WageID'
exec(@sql) 


drop table tb1 , tb2

/*
ID          Name       WageID      WageName1  Wage1                          WageName2  Wage2                          WageName3  Wage3
----------- ---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
1           zy         1           补助         100                            保险         500                            奖金         200
2           zc         2           费用1        50                             费用2        300                                       

(2 行受影响)
*/

[dawugui]

[Quote=引用 5 楼 zhangyang555 的回复:]
子表中的项目是不固定的.有可能是3个,有可能是5个.
[/Quote]
所以要用4楼或者6六的动态SQL.

[zhangyang555]

Tabel1的字段是很多的.
我要查询的话会用select * from table1
不会用 select id,name,wageid...... from table1 这样.
应该怎么处理?

[htl258_Tony]

[Quote=引用 8 楼 zhangyang555 的回复:]
Tabel1的字段是很多的.
我要查询的话会用select * from table1
不会用 select id,name,wageid...... from table1 这样.
应该怎么处理?
[/Quote]
显示表1的所有字段:

declare @s varchar(1000)
select @s=isnull(@s+',','')+name from syscolumns where object_id('表1名称')=id
print @s

[feixianxxx]

[Quote=引用 9 楼 htl258 的回复:]
引用 8 楼 zhangyang555 的回复:
Tabel1的字段是很多的.
我要查询的话会用select * from table1
不会用 select id,name,wageid...... from table1 这样.
应该怎么处理?

显示表1的所有字段:

SQL code
declare @s varchar(1000)
select @s=isnull(@……
[/Quote]
exec ('select '+@s+' from table1')

[zhangyang555]

我是回复 爱新觉罗.毓华 他的代码,他的代码不是这样的吗?:set @sql = 'select m.ID , m.Name , m.WageID '


我的意思我要用 :Select * from table1 这样的方式。

[htl258_Tony]

[Quote=引用 11 楼 zhangyang555 的回复:]
我是回复 爱新觉罗.毓华 他的代码,他的代码不是这样的吗?:set @sql = 'select m.ID , m.Name , m.WageID '


我的意思我要用 :Select * from table1 这样的方式。
[/Quote]
要注意后面的group by ，group by 不支持 *

[feixianxxx]

[Quote=引用 11 楼 zhangyang555 的回复:]
我是回复 爱新觉罗.毓华 他的代码,他的代码不是这样的吗?:set @sql = 'select m.ID , m.Name , m.WageID '


我的意思我要用 :Select * from table1 这样的方式。
[/Quote]
一样啊。。。
把那些需要的放到变量里面 然后拼接到查询语句里

你可以在syscolumns 里面去掉不要的

[zhangyang555]

[Quote=引用 12 楼 htl258 的回复:]
引用 11 楼 zhangyang555 的回复:
我是回复 爱新觉罗.毓华 他的代码,他的代码不是这样的吗?:set @sql = 'select m.ID , m.Name , m.WageID '


我的意思我要用 :Select * from table1 这样的方式。

要注意后面的group by ，group by 不支持 *
[/Quote]
我就是这个意思,我如果用 select * from table1的方式上边的问题应该怎么处理

[htl258_Tony]

[Quote=引用 14 楼 zhangyang555 的回复:]
引用 12 楼 htl258 的回复:
引用 11 楼 zhangyang555 的回复:
我是回复 爱新觉罗.毓华 他的代码,他的代码不是这样的吗?:set @sql = 'select m.ID , m.Name , m.WageID '


我的意思我要用 :Select * from table1 这样的方式。

要注意后面的group by ，group by 不支持 *
……
[/Quote]

create table tb1(ID int,Name nvarchar(10), WageID int)
insert into tb1 values(1, N'zy', 1) 
insert into tb1 values(2, N'zc', 2)

create table tb2(ID int,WageID int,Name nvarchar(10),Wage int)
insert into tb2 values(1 ,1 ,N'补助' ,100)
insert into tb2 values(2 ,1 ,N'保险' ,500)
insert into tb2 values(3 ,1 ,N'奖金' ,200)
insert into tb2 values(4 ,2 ,N'费用1', 50)
insert into tb2 values(5 ,2 ,N'费用2', 300)
go

declare @sql varchar(8000),@s varchar(1000)
select @s=isnull(@s+',m.','m.')+name from syscolumns where object_id('tb1')=id
set @sql = 'select '+@s
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then n.Name else '''' end) [WageName' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(n.Wage as varchar) else '''' end) [Wage' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = row_number() over(partition by WageID order by id) from tb2 t) k) as a
set @sql = @sql + ' from tb1 m , (select t.* , px = row_number() over(partition by WageID order by id) from tb2 t) n where m.WageID = n.WageID group by '+@s
exec(@sql) 

/*
ID          Name       WageID      WageName1  Wage1                          WageName2  Wage2                          WageName3  Wage3
----------- ---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
1           zy         1           补助         100                            保险         500                            奖金         200
2           zc         2           费用1        50                             费用2        300                                       

(2 行受影响)
*/
drop table tb1 , tb2

[zhangyang555]

row_number在sqlserver中不能用吧.

[htl258_Tony]

[Quote=引用 16 楼 zhangyang555 的回复:]
row_number在sqlserver中不能用吧.
[/Quote]2005以上版本用,2000的可以变通的加一自增列或生成临时表处理

[htl258_Tony]

你表有ID，2000的也可以直接查询：



declare @sql varchar(8000),@s varchar(1000)
select @s=isnull(@s+',m.','m.')+name from syscolumns where object_id('tb1')=id
set @sql = 'select '+@s
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then n.Name else '''' end) [WageName' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(n.Wage as varchar) else '''' end) [Wage' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select COUNT(1) from tb2 where WageID =t.WageID and ID<=t.ID) from tb2 t) k) as a
set @sql = @sql + ' from tb1 m , (select t.* , px = (select COUNT(1) from tb2 where WageID =t.WageID and ID<=t.ID) from tb2 t) n where m.WageID = n.WageID group by '+@s
exec(@sql) 
/*
ID          Name       WageID      WageName1  Wage1                          WageName2  Wage2                          WageName3  Wage3
----------- ---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
1           zy         1           补助         100                            保险         500                            奖金         200
2           zc         2           费用1        50                             费用2        300                                       

(2 行受影响)
*/