这种效果如何实现 求sql高手
0
最佳答案
0
sql如果要做不定列的查询,只能通过拼接sql来实现吧,而且你的列明显会比行多很多,建议行列颠倒一下
收获园豆：25
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
呵呵 这个领导规定死的 我没办法说来改的。谢谢你了 我再等等看看 还有别的解决方案吗 我感觉这种就是sql太长了 我之前写的行转列的和你这个差不多 都是特别长 所以想看看还有没有别的解决方案。
@小 莫: 上千个用户的时候列数不可想象...
其他回答(1)
0
我觉得用视图应该简单些
收获园豆：5
