常用到的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

 

3、计算累积销售额

id	name
1	a
2	b
3	c
4	d
5	e

 

表a

 

　　1）普通sql解决

SELECT a.*,sum(b.id) FROM a join a b  on a.id >= b.id GROUP BY a.id

 

 　　2）窗口函数解决

select a.* ,sum(id) over(ORDER BY name)  from  a 

 

结果

 

4、连续几天用户登录

　　

 

 连续2天用户登录

select * from (
SELECT
    *,
    lead(login_data,1) over ( PARTITION BY user_id ORDER BY login_data ) AS rn 
FROM
    last_3days ) a where DATEDIFF(rn,login_data) = 1

 

 连续3天用户登录

select * from (
SELECT
    *,
    lead(login_data,2) over ( PARTITION BY user_id ORDER BY login_data ) AS rn 
FROM
    last_3days ) a where DATEDIFF(rn,login_data) = 2