sql语句

汇总SQL语句。主要是自学用，也欢迎网友们查看和使用，持续更新。

1.case when

简单例子

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        ELSE '异常' END) AS REMARK
FROM
    TABL

未完

2 row_number() over

例子

select ROW_NUMBER() OVER (ORDER BY StudentScore DESC) number ，* from table1 t1  --按StudentScore降序排列，并为每一条数据加一个序号

与rank()、dense_rank()的区别

--对90，90，85排序
select ROW_NUMBER() OVER (ORDER BY StudentScore DESC) number ，* from table1 t1 --123
select dense_rank() OVER (ORDER BY StudentScore DESC) number ，* from table1 t1  --112
select RANK() OVER (ORDER BY StudentScore DESC) number ，* from table1 t1  --113

3 显示分区字段

show PARTITIONS table_name

4 left join

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name