你见过哪些有意思的SQL语句？

谢邀！有意思的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;