小虾米SQL的一个query求指点

Leaveland

今天小虾米想写一个存储过程完成一项功能的时候
有一个看起来简单但实际上挠了一下午头也没想出来怎么做好

比如一个record表
Field A是integer可能是任何整数 其他Field都是字符串

FieldA  FieldB  FieldC
3           John  Smith
2           Lily    Smith

想要实现的是 run一个SP 对每一条记录检查FieldA的值 如果值n大于1 则复制该记录n次同时把FieldA的值从n一直排列到1

比如上面那个表最终update成这样

FieldA  FieldB  FieldC
3           John  Smith
2           John  Smith
1           John  Smith
2           Lily    Smith
1           Lily    Smith

是不是把复制的记录都select到一个临时表去然后再合并呢？

query复杂程度系统花销当然越低越好
IT版高人多 还望能指点迷津 先谢谢了！

yolandalinz

我怎么看着像一个小程序应该干的事。。。有跟多需要理清，比如表里面有没有Duplicate Item，如果碰到应该怎么处理？

qyz225

is it what you need? TSql recursive cte:


tsql recursive cte


use staging

declare @t table (FieldA int, FieldB varchar(50), FieldC varchar(50))

insert into @t
values
(3, 'John', 'Smith'),
(2, 'John', 'Smith'),
(2, 'Jack', 'London')

; with t1 as
(
select t.fielda, t.fieldb, t.fieldc, row_number() over (partition by fieldb, fieldc order by fielda) as ord
from @t t
)
, t2 as
(
select fielda, fieldb, fieldc
from t1
where ord = 1
union all
select fielda-1, fieldb, fieldc
from t2
where fielda > 1
)

select * from t2
union all
select fielda, fieldb, fieldc
from t1
where ord > 1
order by fieldb, fieldc, fielda desc


it is faster than while loop, but it could still be very slow if the table is too large, so it really depends on your business case...

月亮

有很多方法做，但如果考虑最优的方法，Record表的大小直接影响用什么方法做

Leaveland

qyz225 发表于 2015-7-28 18:44
is it what you need? TSql recursive cte:

太有才了

我也是想尽量避免loop
看起来不错

我明天实测一下看看效率

Leaveland

月亮 发表于 2015-7-28 18:49
有很多方法做，但如果考虑最优的方法，Record表的大小直接影响用什么方法做 ...

咋说呢

Record表的大小可能变化会非常大

跑SP之前也许只有几千条记录
但是那个int项的值 存在理论可能性会很大
所以表可能会膨胀得很厉害

Fernando

表的基本情况，主键，索引，row cnt，读写状况，没有说
应用场景也要说清楚
很多时候loop就loop，膨胀就膨胀，效率低一点没太大关系。首先你要先定义好你需要的返回时间

wonderdream

有一种东西叫 Tally table:

--Declare TestTable Variable
declare @t table (FieldA int, FieldB varchar(50), FieldC varchar(50))
insert into @t
values
(3, 'John', 'Smith'),
(2, 'John', 'Smith'),
(2, 'Jack', 'London')

--Genera Tally table (for SQL 2008+)
;WITH Tally (n) AS
(
    -- 1000 rows (10 x 10 x 10)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)
SELECT
        --t.FieldA,
        l.n,
        t.FieldB,
        t.FieldC
FROM @t t
        LEFT JOIN Tally l ON t.FieldA>=l.n
--ORDER by t.FieldA, t.FieldB, t.FieldC, l.n desc /* uncomment order clause in dev only */


T-SQL用set based operation是最快的。在使用loop和cte之前，先想想能不能用set based operation实现。

顺带一提，我公司招Senior Microsoft BI Developer, SSAS Tabular, SSIS, SSRS, Spark on HDInsight, Azure IaaS, PaaS, Power Shell. 如有兴趣，发我邮件： george.qiao@staples.com.au

浮云马

好多高人。。。膜拜

stevenbian

google sql server select 1 to n
最简单不用循环的貌似是找个大表抓行数