记录工作中经常使用的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. 左连接
1
select * from table1 t1 left join table2 t2 on t1.col=t2.col
  1. 右连接
1
select * from table1 t1 right join table2 t2 on t1.col=t2.col
  1. 内连接
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. 外连接
1
select * from table1 t1 outer join table2 t2 on t1.col=t2.col

SQL联合

  1. 允许重复值
1
2
3
SELECT col FROM table1
UNION ALL
SELECT col FROM table2;
  1. 不允许重复值
1
2
3
SELECT col FROM table1
UNION
SELECT col FROM table2;

分组

  1. GROUP BY
1
SELECT col FROM table1 group by col

排序

  1. row_number 依次排序
1
SELECT col1,row_number() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

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

  1. rank 跳跃排序
1
SELECT col1,rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

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

  1. dense_rank 叠加排序
1
SELECT col1,dense_rank() over(PARTITION BY col2 ORDER BY col3) as t FROM table1

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

  1. ntile 按序排序
1
SELECT col1,ntile(num) over(PARTITION BY col2 ORDER BY col3) as t FROM table1

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

  1. 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

  1. 以abc结尾
1
SELECT col FROM table where col like '%abc'
  1. 以abc开始
1
SELECT col FROM table where col like 'abc%'
  1. 包含abc
1
SELECT col FROM table where col like '%abc%'
  1. 通配符可点击下方链接前往查看

字符连接

使用’||’

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

IN && EXISTS

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

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

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