【转】面试之BI-SQL--table转换

[guguda2008]

刚在别的地方看到的，还挺好玩，无聊的同学可以试试。

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

[guguda2008]

引用 2 楼 ap0405140 的回复:

唐诗不才,弱弱的问一下,这是什么规律.

这题考的就是找规律啊嘎嘎嘎嘎，实现其实很简单

[詩和遠方]

引用 2 楼 ap0405140 的回复:

唐诗不才,弱弱的问一下,这是什么规律.

牛逼呀！！！

[詩和遠方]

引用 3 楼 wufeng4552 的回复:

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 --没测试

额，是这个很牛逼！！！

[火拼阿三]

真的太厉害了。。。还没看出规律。。。

[--小F--]

好像是断号查询。恩恩

[奔四在望]

引用 3 楼 wufeng4552 的回复:

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 --没测试

牛逼

[--小F--]

----------------------------------------------------------------
-- 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 行受影响)
*/

[chuanzhang5687]

数字的连续性

[guguda2008]

就是断号查询，原文解答SQL如下 selectMIN(Num),MAX(Num)from (selectROW_NUMBER()over (orderbyNum)rk,Num FromNumbers)A Groupby (A.rk-A.Num) orderbyMin(Num)

[詩和遠方]

引用 9 楼 fredrickhu 的回复:

好像是断号查询。恩恩

我们还没反应过来，大神已经知道问题的类别了，我会告诉你“断号查询”这个名词我从没听过吗，大神果然够经验！！！

[飞啊子]

--->这个是 缺失范围查询

--> 测试数据：[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;