一个sql的行列转置的例子
4103 点击·0 回帖
灯火互联
楼主
 |  | |
 | 一个sql的行列转置的例子 有这样一个AAA表如下所示： name score color jim 10 red jim 20 blue jim 20 green jim 1 black glin 2 red glin 33 blue glin 21 green glin 19 black bob 22 red bob 39 blue bob 11 green bob 11 black www.atcpu.com 要转置成如下所示的BBB表 name red blue green black jim 10 20 20 1 bob 22 39 11 11 glin 2 33 21 19 使用的sql如下： select R.name ,R.s red,B.s blue,G.s green ,Bk.s black from( select name,sum(score)s from AAA where color='red' group by name ) R, ( select name,sum(score)s from AAA where color='blue' group by name ) B, ( select name,sum(score)s from AAA where color='green' group by name ) G, ( select name,sum(score)s from AAA where color='black' group by name ) Bk where R.name = B.name and B.name = G.name and G.name = Bk.name 相当于group by之后把每个人的某一种颜色的分数统计成一个数据集，再把这几个响应的数据集做表连接拼起来。 www.atcpu.com 如果反过来，指导BBB表，要返回成AAA表，sql的写法是怎样呢？ select name, red score,'red' color from BBB union select name, blue score,'blue' color from BBB union select name, green score,'green' color from BBB union select name, black score,'black' color from BBB 就是把几个查询集并起来。 | |
 |  |