汇总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