这些SQL在工作中非常实用(HANA)！

记录工作中经常使用的SQL(基于HANA),查询为主!如有补充，请评论留言！

SQL查询

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

2. 右连接

select * from table1 t1 right join table2 t2 on t1.col=t2.col

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

4. 外连接

select * from table1 t1 outer join table2 t2 on t1.col=t2.col

SQL联合

1. 允许重复值

SELECT col FROM table1
UNION ALL
SELECT col FROM table2;

2. 不允许重复值

SELECT col FROM table1
UNION
SELECT col FROM table2;

分组

1. GROUP BY

SELECT col FROM table1 group by col

排序

1. row_number 依次排序

SELECT col1,row_number() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

例子:1,2,3,4,5,6

3. rank 跳跃排序

SELECT col1,rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

例子:1,2,2,4,5,6

3. dense_rank 叠加排序

SELECT col1,dense_rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

例子:1,2,2,3,4,5

4. ntile 按序排序

SELECT col1,ntile(num) over(PARTITION BY col2 ORDER BY col3) as t FROM table1

例子:1,1,1,2,2,2

5. order by 直接排序

降序

SELECT col1 FROM table1 order by col1 desc//降序

升序

SELECT col1 FROM table1 order by col1 asc//升序

为空/不为空

为空

SELECT col FROM table where col is null 

不为空

SELECT col FROM table where col is not null 

LIKE

1. 以abc结尾

SELECT col FROM table where col like '%abc'

2. 以abc开始

SELECT col FROM table where col like 'abc%'

3. 包含abc

SELECT col FROM table where col like '%abc%'

4. 通配符可点击下方链接前往查看

字符连接

使用’||’

SELECT col,col2 FROM table where col = 'pre'||col2

IN && EXISTS

1. IN

SELECT * FROM table
WHERE name IN ('a','b');

查询结果为name包含a,b的数据

2. EXISTS

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()等等，具体点击下方链接: