谢邀!有意思的SQL可多了,毕竟数据这个东西玩儿起来,只有想不到,没有做不到。
做到做不到?一试便知道!
1、WHERE 1 = 2 复制表结构
create table test_table_a as select * from test_table_b where 1 = 2;
注意:这种复制表结构的方式,不会复制主键和索引
2、窗口函数 OVER(PARTITION BY ...)
避免了使用group by必须将所有分组字段逐个写到select中的烦恼,常用于多维度的排序筛选。
例如:查询物料最新采购价格。
--创建临时表,插入测试数据
if Object_id('Tempdb..#temp1') is not null drop table #temp1
create table #temp1(ItemNumber varchar(10),PurchDate date,PurchPrice decimal(10,2))
insert into #temp1(ItemNumber,PurchDate,PurchPrice)
select 'Item01', '2016-1-8',3.33 union all
select 'Item01', '2016-5-8',2.22 union all
select 'Item01', '2016-3-8',1.11 union all
select 'Item02', '2016-3-9',4.44 union all
select 'Item02', '2016-5-9',5.55 union all
select 'Item02', '2016-1-9',6.66 union all
select 'Item03', '2016-1-7',9.99 union all
select 'Item03', '2016-3-7',8.88 union all
select 'Item03', '2016-3-7',7.77
--查询所有信息
select * from #temp1 order by ItemNumber asc,PurchDate desc,PurchPrice asc
--使用row_number() over partition by 查询物料的最新采购价格,如果同一日期有多个价格取最小值
select *
from (
select ItemNumber,PurchDate,PurchPrice,
row_number() over(partition by ItemNumber order by ItemNumber asc,PurchDate desc,PurchPrice asc) row_num
from #temp1) t1
where t1.row_num=1
3、WITH AS 子查询进行递归查询
常用于查询树状结果,例如菜单树、BOM树。
with x1(level_,union_menu_id,title_path,title,name,parent) as (
select 1 as level_,
case length(t1.menu_id) when 1 then '000'||t1.menu_id
when 2 then '00' ||t1.menu_id
when 3 then '0' ||t1.menu_id
else to_char(t1.menu_id) end as union_menu_id,
t1.title title_path,
t1.title,
t1.name,
t1.parent
from menu_table t1 where t1.name in (select a.name from menu_table a where a.parent is null)
union all
select x1.level_ + 1,
case length(x1.union_menu_id) when 1 then '000'||x1.union_menu_id
when 2 then '00' ||x1.union_menu_id
when 3 then '0' ||x1.union_menu_id
else to_char(x1.union_menu_id) end ||'/'||
case length(t2.menu_id) when 1 then '000'||t2.menu_id
when 2 then '00' ||t2.menu_id
when 3 then '0' ||t2.menu_id
else to_char(t2.menu_id) end as union_menu_id,
x1.title_path||'->'||t2.title title_path,
t2.title,
t2.name,
t2.parent
from x1,menu_table t2
where x1.name = t2.parent )
select * from x1 order by x1.union_menu_id;
4、start with connect by prior递归查询(Oracle)
与上面的WITH AS一样,用于递归查询树状结构。
select * from (
select level,
sys_connect_by_path(
case length(t1.menu_id) when 1 then '000'||t1.menu_id
when 2 then '00' ||t1.menu_id
when 3 then '0' ||t1.menu_id
else to_char(t1.menu_id) end,'/') union_menu_id,
sys_connect_by_path(title,'->') title_path,
t1.title,
t1.name,
t1.parent,
t1.location
from menu_table t1
where 1 = 1
start with name in (select a.name from menu_table a where a.parent is null)
connect by prior name = parent ) x1
order by x1.union_menu_id;
5、物化视图
从远程dblink创建本地物化视图,按需刷新数据,以提高访问速度。
CREATE MATERIALIZED VIEW v_test_view
REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT TRUNC(SYSDATE, 'mi') + 1 / 6
AS
SELECT * FROM test_user.v_remote_test_view@db_link_name;