# 这种效果如何实现 求sql高手

0

0

sql如果要做不定列的查询,只能通过拼接sql来实现吧,而且你的列明显会比行多很多,建议行列颠倒一下

```create table _20130318_users(userid int identity(1,1),username nvarchar(50))
create table _20130318_user_height_his(id int identity(1,1),userid int,year int,height decimal(20,2))
create table _20130318_user_weight_his(id int identity(1,1),userid int,year int,weight decimal(20,2))
insert into _20130318_users values('张三')
insert into _20130318_users values('李四')
insert into _20130318_users values('王五')
--select * from _20130318_users
insert into _20130318_user_height_his values(1,2011,177)
insert into _20130318_user_height_his values(1,2012,178)
insert into _20130318_user_height_his values(1,2013,179)
insert into _20130318_user_height_his values(2,2011,170)
insert into _20130318_user_height_his values(2,2012,171)
insert into _20130318_user_height_his values(2,2013,172)
insert into _20130318_user_height_his values(3,2011,180)
insert into _20130318_user_height_his values(3,2012,179)
insert into _20130318_user_height_his values(3,2013,178)
--select * from _20130318_user_height_his
insert into _20130318_user_weight_his values(1,2011,50)
insert into _20130318_user_weight_his values(1,2012,50.5)
insert into _20130318_user_weight_his values(1,2013,51)
insert into _20130318_user_weight_his values(2,2011,75)
insert into _20130318_user_weight_his values(2,2012,75.5)
insert into _20130318_user_weight_his values(2,2013,81)
insert into _20130318_user_weight_his values(3,2011,70)
insert into _20130318_user_weight_his values(3,2012,60.5)
insert into _20130318_user_weight_his values(3,2013,51)
--select * from _20130318_user_weight_his
select username,height_2011,height_2012,height_2013,weight_2011,weight_2012,weight_2013 from _20130318_users a left join (select userid,sum(case when year=2011 then height else 0 end) as height_2011,sum(case when year=2012 then height else 0 end) as height_2012,sum(case when year=2013 then height else 0 end) as height_2013 from _20130318_user_height_his group by userid) b on a.userid=b.userid left join (select userid,sum(case when year=2011 then weight else 0 end) as weight_2011,sum(case when year=2012 then weight else 0 end) as weight_2012,sum(case when year=2013 then weight else 0 end) as weight_2013 from _20130318_user_weight_his group by userid) c on a.userid=c.userid```

@小 莫: 上千个用户的时候列数不可想象...

0

您需要登录以后才能回答，未注册用户请先注册