记录工作中经常使用的SQL(基于HANA),查询为主!如有补充，请评论留言！
SQL查询
1
2
3
4
5
6
7
| select * from table //字段全部查询
select col from table //查询指定字段
select col as at from table //别名
select DISTINCT col from table //去重（影响性能，慎用）
|
SQL连接
常用连接为左连接、右连接，内连接，外连接
- 左连接
1
| select * from table1 t1 left join table2 t2 on t1.col=t2.col
|
- 右连接
1
| select * from table1 t1 right join table2 t2 on t1.col=t2.col
|
- 内连接
1
2
3
| select * from table1 t1 inner join table2 t2 on t1.col=t2.col
select * from table1 t1 join table2 t2 on t1.col=t2.col
|
- 外连接
1
| select * from table1 t1 outer join table2 t2 on t1.col=t2.col
|
SQL联合
- 允许重复值
1
2
3
| SELECT col FROM table1
UNION ALL
SELECT col FROM table2;
|
- 不允许重复值
1
2
3
| SELECT col FROM table1
UNION
SELECT col FROM table2;
|
分组
- GROUP BY
1
| SELECT col FROM table1 group by col
|
排序
- row_number 依次排序
1
| SELECT col1,row_number() over(PARTITION BY col2 ORDER BY col3) as t FROM table1
|
例子:1,2,3,4,5,6
- rank 跳跃排序
1
| SELECT col1,rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1
|
例子:1,2,2,4,5,6
- dense_rank 叠加排序
1
| SELECT col1,dense_rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1
|
例子:1,2,2,3,4,5
- ntile 按序排序
1
| SELECT col1,ntile(num) over(PARTITION BY col2 ORDER BY col3) as t FROM table1
|
例子:1,1,1,2,2,2
- order by 直接排序
降序
1
| SELECT col1 FROM table1 order by col1 desc//降序
|
升序
1
| SELECT col1 FROM table1 order by col1 asc//升序
|
为空/不为空
为空
1
| SELECT col FROM table where col is null
|
不为空
1
| SELECT col FROM table where col is not null
|
LIKE
- 以abc结尾
1
| SELECT col FROM table where col like '%abc'
|
- 以abc开始
1
| SELECT col FROM table where col like 'abc%'
|
- 包含abc
1
| SELECT col FROM table where col like '%abc%'
|
- 通配符可点击下方链接前往查看
字符连接
使用’||’
1
| SELECT col,col2 FROM table where col = 'pre'||col2
|
IN && EXISTS
- IN
1
2
| SELECT * FROM table
WHERE name IN ('a','b');
|
查询结果为name包含a,b的数据
- EXISTS
1
2
3
| SELECT *
FROM table1
WHERE EXISTS (SELECT count FROM table2 WHERE table1.col = table2.col AND table2.col2 > 100);
|
查询是否存在col2大于100的数据
函数
常用函数有COUNT()、SUM()、AVG()、ROUNG()、ADD_SECONDS()、MIN()、MAX()等等，具体点击下方链接:
