【转】面试之BI-SQL--table转换
刚在别的地方看到的，还挺好玩，无聊的同学可以试试。
Num
1
2
4
6
7
8
10
11
13
写条SQL语句转成下表：
Column1 Column2
1 2
4 4
6 8
10 11
13 13
[解决办法]
select
min(t.Column1)Column1,
max(t.Column1)Column2
from(
select Column1,cnt=Column1-row_number()over(order by getdate()) from tb
)t group by cnt
--没测试
[解决办法]
select a.Num as Column1
(select top 1 b.num from test b
where b.num >= a.Num
and not exists (
select 1 from test
where Num = b.Num + 1
)
order by b.num asc
) as Column2
from test a
where not exists (
select 1 from test
where Num = a.Num - 1
)
order by 1 asc
[解决办法]
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-10-17 11:32:39
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int)
insert [tb]
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 7 union all
select 8 union all
select 10 union all
select 11 union all
select 13
--------------开始查询--------------------------
select
rtrim(a.num) AS columus1,(case when min(b.num)!=a.num then rtrim(min(b.num)) else a.num end) AS columus2
from
(select t.num from tb t where not exists(select 1 from tb where num=t.num-1)) a,
(select t.num from tb t where not exists(select 1 from tb where num=t.num+1)) b
where
a.num<=b.num
group by
a.num
----------------结果----------------------------
/* columus1 columus2
------------ -----------
1 2
4 4
6 8
10 11
13 13
(5 行受影响)
*/
--->这个是 缺失范围查询
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int)
insert [tb]
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 7 union all
select 8 union all
select 10 union all
select 11 union all
select 13
select cur+1 as startNum,nxt-1 as endNum
from(
select Num as cur,(select MIN(Num) from tb lt1 where t.Num<lt1.Num) as nxt
from tb t
) p
where nxt-cur>1;
