1、在表中检索出来的数据前后添加字符
eg:
<pre class="sql" name="code">table
id name ps
1 demo 123456
2 lions 123456
要检索出"土豪的demo","lions是穷逼"
select '土豪的'||t.name from table t;
select t.name||'是穷逼' from table t;
裸照用遇到这个问题是在表的迁移时,需要在所有的属性前加上前缀,此方法还是很有效的
在sql中用的是
select concat('土豪的',t.name) from table t
select concat(t.name,'是穷逼',) from table t
2、字符的截取
eg:
id name ps
1 土豪的demo 123456
2 lions是傻逼 123456
只要检索出demo和lions
select substr(t.name,4) from table t
select substr(t.name,0,5) from table t
3、集合
eg:
table1
id name ps
1 demo 123456
2 lions 123456
table2
id name ps
1 demo 123456
2 zebra 123456
只要检索出zebra和lions
<span style="color: #000000;">常用并集union、union all,但是被问到差集怎么查询,第一反应是</span>
select name from table1 where name not in(select name from table2)
union
select name from table2 where name not in(select name from table1)
但是,想想这样子的效率好像有点问题啊多个select还要where not in,效率肯定是有点问题的
然后就查询了一下,发现了居然还有<strong><span style="color: #ff0000;">minus</span></strong>这个关键字。如下:
select name from table1 minus select name from table2
union
select name from table2 minus select name from table1
额,我想为什么没有直接一个关键字就可以解决差集的问题呢
4、更新还是插入:
数据name=demo,在数据库层次判断是要插入还是更新,当数据库已经存在name=demo时,则为修改,否则为插入
table
id name ps
1 demo 123456
2 lions 123456
merge into table d
using (select name from table where name='demo')s
on(d.name=s.name)
when matched then
update set d.ps='123'
when not matched then
insert(d.name,d.ps)values('demo','123');
5、深度查询:
在一张表内存在id 和parented,根据id找到所有的子孙节点,这是一个深度迭代
WITH tab AS (
SELECT
DepartmentId,
ParentId
FROM
Base_Department
WHERE
DepartmentId = '' --父节点
UNION ALL
SELECT
b.DepartmentId,
b.ParentId
FROM
tab a,--父节点数据集
Base_Department b --子节点数据集
WHERE
b.ParentId = a.DepartmentId --子节点数据集.ID=父节点数据集.parendID
)select * from OA_EmpLeave where DepartmentId in( SELECT
tab.DepartmentId
FROM
tab);
6、广度迭代和深度迭代:
广度遍历:select * from table start with pid = 0 connect by prior id = pid order by level;