SQL分拆數量,看看有沒有簡單高效的方法

Question

create table test([name] varchar(10),code varchar(05),[date] datetime,quantity int,qty int)
insert into test 
select 'A','MS','2006/09/20',3100,1500  union all 
select 'B','MS','2006/09/23',27003,1500  union all
select 'C','MS','2006/09/27',400,1500    union all
select 'D','ND','2006/09/27',2000,1300  
 /*其中Key: [name]+code+[date] ,( 不過實際上,[name]的值也是唯一的).
  拆分後得到如下內容:*/name        code           date          break_quantity
-------------------------------------------------------------------------------
A_0001      MS          2006/09/20        1500
A_0002      MS          2006/09/20        1500
A_0003      MS          2006/09/20        100
B_0001      MS          2006/09/23        1500
B_0002      MS          2006/09/23        1500
....(省略B0003-B0018)
....
B_0019      MS          2006/09/23        3
C_0001      MS          2006/09/27        400
D_0001      ND          2006/09/27        1300
D_0002      ND          2006/09/27        700
   各位大大看看有沒有高效點的方法,我現在用cursor來處理,雖然速度還可以,還是想看看能不能不用cursor

Answer 1

if object_id('test') is not null drop table test
go
create table test([name] varchar(10),code varchar(05),[date] datetime,quantity int,qty int)
insert into test 
select 'A','MS','2006/09/20',3100,1500  union all 
select 'B','MS','2006/09/23',27003,1500  union all
select 'C','MS','2006/09/27',400,1500    union all
select 'D','ND','2006/09/27',2000,1300  
-----------------------------------------------------
declare @i int, @n int
select @n = max((quantity - 1)/qty + 1) from test
print @n
set @i = 1
if object_id('tempdb..#') is not null drop table #
select 9999 as [Id] into # where 1 = 0
while @i <= @n  
begin
  insert into # select @i
  set @i = @i + 1
end
select (a.[name] + '_' + right(cast(10000 + b.[Id] as varchar), 4)) as [name], a.code, a.[date],
  (case when (a.quantity - 1)/a.qty + 1 = b.[Id] then a.quantity % a.qty else a.qty end) as break_quantity
from test a join # b on (a.quantity - 1)/a.qty + 1 >= b.[Id]
/*
name       code  date                      break_quantity 
---------- ----- ------------------------- -------------- 
A_0001     MS    2006-09-20 00:00:00.000   1500
A_0002     MS    2006-09-20 00:00:00.000   1500
A_0003     MS    2006-09-20 00:00:00.000   100
B_0001     MS    2006-09-23 00:00:00.000   1500
B_0002     MS    2006-09-23 00:00:00.000   1500
B_0003     MS    2006-09-23 00:00:00.000   1500
B_0004     MS    2006-09-23 00:00:00.000   1500
B_0005     MS    2006-09-23 00:00:00.000   1500
B_0006     MS    2006-09-23 00:00:00.000   1500
B_0007     MS    2006-09-23 00:00:00.000   1500
B_0008     MS    2006-09-23 00:00:00.000   1500
B_0009     MS    2006-09-23 00:00:00.000   1500
B_0010     MS    2006-09-23 00:00:00.000   1500
B_0011     MS    2006-09-23 00:00:00.000   1500
B_0012     MS    2006-09-23 00:00:00.000   1500
B_0013     MS    2006-09-23 00:00:00.000   1500
B_0014     MS    2006-09-23 00:00:00.000   1500
B_0015     MS    2006-09-23 00:00:00.000   1500
B_0016     MS    2006-09-23 00:00:00.000   1500
B_0017     MS    2006-09-23 00:00:00.000   1500
B_0018     MS    2006-09-23 00:00:00.000   1500
B_0019     MS    2006-09-23 00:00:00.000   3
C_0001     MS    2006-09-27 00:00:00.000   400
D_0001     ND    2006-09-27 00:00:00.000   1300
D_0002     ND    2006-09-27 00:00:00.000   700*/
drop table #
-----------------------------------------------------
drop table testLZ试试效果，看看速度如何

Answer 2

--補充樓上的語句,否則改D:  'D','ND','2006/09/27',2600,1300的時候會出錯select (a.[name] + '_' + right(cast(10000 + b.[Id] as varchar), 4)) as [name], a.code, a.[date],
  (case when (a.quantity - 1)/a.qty + 1 = b.[Id] 
then when a.quantity % a.qty=0 then a.qty else  a.quantity % a.qty end   ----補充這句
else a.qty end) as break_quantity
from test a join # b on (a.quantity - 1)/a.qty + 1 >= b.[Id]

Answer 3

没注意，改一下：
if object_id('test') is not null drop table test
go
create table test([name] varchar(10),code varchar(05),[date] datetime,quantity int,qty int)
insert into test 
select 'A','MS','2006/09/20',3100,1500  union all 
select 'B','MS','2006/09/23',27003,1500  union all
select 'C','MS','2006/09/27',400,1500    union all
select 'D','ND','2006/09/27',2600,1300  
-----------------------------------------------------
declare @i int, @n int
select @n = max((quantity - 1)/qty + 1) from test
print @n
set @i = 1
if object_id('tempdb..#') is not null drop table #
select 9999 as [Id] into # where 1 = 0
while @i <= @n  
begin
  insert into # select @i
  set @i = @i + 1
end
select (a.[name] + '_' + right(cast(10000 + b.[Id] as varchar), 4)) as [name], a.code, a.[date],
  (case when (a.quantity - 1)/a.qty + 1 = b.[Id] then (a.quantity - 1) % a.qty + 1 else a.qty end) as break_quantity
from test a join # b on (a.quantity - 1)/a.qty + 1 >= b.[Id]
/*
name       code  date                      break_quantity 
---------- ----- ------------------------- -------------- 
A_0001     MS    2006-09-20 00:00:00.000   1500
A_0002     MS    2006-09-20 00:00:00.000   1500
A_0003     MS    2006-09-20 00:00:00.000   100
B_0001     MS    2006-09-23 00:00:00.000   1500
B_0002     MS    2006-09-23 00:00:00.000   1500
B_0003     MS    2006-09-23 00:00:00.000   1500
B_0004     MS    2006-09-23 00:00:00.000   1500
B_0005     MS    2006-09-23 00:00:00.000   1500
B_0006     MS    2006-09-23 00:00:00.000   1500
B_0007     MS    2006-09-23 00:00:00.000   1500
B_0008     MS    2006-09-23 00:00:00.000   1500
B_0009     MS    2006-09-23 00:00:00.000   1500
B_0010     MS    2006-09-23 00:00:00.000   1500
B_0011     MS    2006-09-23 00:00:00.000   1500
B_0012     MS    2006-09-23 00:00:00.000   1500
B_0013     MS    2006-09-23 00:00:00.000   1500
B_0014     MS    2006-09-23 00:00:00.000   1500
B_0015     MS    2006-09-23 00:00:00.000   1500
B_0016     MS    2006-09-23 00:00:00.000   1500
B_0017     MS    2006-09-23 00:00:00.000   1500
B_0018     MS    2006-09-23 00:00:00.000   1500
B_0019     MS    2006-09-23 00:00:00.000   3
C_0001     MS    2006-09-27 00:00:00.000   400
D_0001     ND    2006-09-27 00:00:00.000   1300
D_0002     ND    2006-09-27 00:00:00.000   700*/
drop table #
-----------------------------------------------------
drop table test

Answer 4

没注意，改一下：
if object_id('test') is not null drop table test
go
create table test([name] varchar(10),code varchar(05),[date] datetime,quantity int,qty int)
insert into test 
select 'A','MS','2006/09/20',3100,1500  union all 
select 'B','MS','2006/09/23',27003,1500  union all
select 'C','MS','2006/09/27',400,1500    union all
select 'D','ND','2006/09/27',2600,1300  
-----------------------------------------------------
declare @i int, @n int
select @n = max((quantity - 1)/qty + 1) from test
print @n
set @i = 1
if object_id('tempdb..#') is not null drop table #
select 9999 as [Id] into # where 1 = 0
while @i <= @n  
begin
  insert into # select @i
  set @i = @i + 1
end
select (a.[name] + '_' + right(cast(10000 + b.[Id] as varchar), 4)) as [name], a.code, a.[date],
  (case when (a.quantity - 1)/a.qty + 1 = b.[Id] then (a.quantity - 1) % a.qty + 1 else a.qty end) as break_quantity
from test a join # b on (a.quantity - 1)/a.qty + 1 >= b.[Id]
/*
name       code  date                      break_quantity 
---------- ----- ------------------------- -------------- 
A_0001     MS    2006-09-20 00:00:00.000   1500
A_0002     MS    2006-09-20 00:00:00.000   1500
A_0003     MS    2006-09-20 00:00:00.000   100
B_0001     MS    2006-09-23 00:00:00.000   1500
B_0002     MS    2006-09-23 00:00:00.000   1500
B_0003     MS    2006-09-23 00:00:00.000   1500
B_0004     MS    2006-09-23 00:00:00.000   1500
B_0005     MS    2006-09-23 00:00:00.000   1500
B_0006     MS    2006-09-23 00:00:00.000   1500
B_0007     MS    2006-09-23 00:00:00.000   1500
B_0008     MS    2006-09-23 00:00:00.000   1500
B_0009     MS    2006-09-23 00:00:00.000   1500
B_0010     MS    2006-09-23 00:00:00.000   1500
B_0011     MS    2006-09-23 00:00:00.000   1500
B_0012     MS    2006-09-23 00:00:00.000   1500
B_0013     MS    2006-09-23 00:00:00.000   1500
B_0014     MS    2006-09-23 00:00:00.000   1500
B_0015     MS    2006-09-23 00:00:00.000   1500
B_0016     MS    2006-09-23 00:00:00.000   1500
B_0017     MS    2006-09-23 00:00:00.000   1500
B_0018     MS    2006-09-23 00:00:00.000   1500
B_0019     MS    2006-09-23 00:00:00.000   3
C_0001     MS    2006-09-27 00:00:00.000   400
D_0001     ND    2006-09-27 00:00:00.000   1300
D_0002     ND    2006-09-27 00:00:00.000   1300*/
drop table #
-----------------------------------------------------
drop table test