常用SQL

 

1、行转列，列转行，图一转图二或图二转图一

图一：

Nam	Course	Score
zhangsan	Chinese	85
zhangsan	Maths	76
zhangsan	English	80
lisi	Chinese	82
lisi	Maths	90
lisi	English	55

图二：

Nam	Chinese	Maths	English
zhangsan	85	76	80
lisi	82	90	55

图一转图二：

SELECT
    Nam,
    SUM(IF (Course = 'Chinese', Score, 0)) Chinese,
    SUM(IF (Course = 'Maths', Score, 0)) Maths,
    SUM(IF (Course = 'English', Score, 0)) English
FROM t
GROUP BY Nam

 

图二转图一：

(SELECT Nam, 'Chinese'Course, Chinese Score FROM t2)
UNION
(SELECT Nam, 'Maths' Course , Maths Score FROM t2)
UNION
(SELECT Nam, 'English' Course , English Score FROM t2)
ORDER BY Nam DESC

 2、按照部门分组，显示每个部门的工资等级

部门ID	工资
2	7
2	9
2	4
3	7
4	6
4	8

SELECT *, Row_Number() OVER (partition by 部门ID ORDER BY 工资 desc) R FROM 表名

部门ID	工资	排名
2	9	1
2	7	2
2	4	3
3	7	1
4	8	1
4	6	2