行转列

给定一个数据库的表Test，表的内容如图所示。
| id | class | score |
| ------------- |:-------------:| -----:|
| 1 | one | 91 |
| 2 | one | 92 |
| 3 | two | 93 |
| 4 | two | 94 |

要求写出一个SQL语句，得到如下的结果。

one	two
2	2

 

 

 

思路

select class, count(*) from Test group by class

得到

one	2
two	2

 

 

再行转列

with t1 as (
　　select class, count(*) as num from Test group by class
)

select
sum(case class when 'one'  then num else 0 end) as one,
sum(case class when 'two'  then num else 0 end) as two
from t1

 

方法二：

select
 sum(case class when 'one'  then 1 else 0 end) as one,
 sum(case class when 'two'  then 1 else 0 end) as two
from Test

 

方法三：

SELECT * FROM
  (
        select class, count(*) as num from Test group by class
  ) t
  PIVOT (
      SUM(num)
      FOR class IN ([one],[two])
  ) p