如何根据条件将订单拆分成需要的形式？

[yiminglian]

以下是将数量拆分成1条的代码

select 订单编号, 名称,类,型号,值, 1 as 数量, 颜色7
from test ,
(
SELECT [number] FROM [master].dbo.[spt_values] WHERE [name] IS NULL and number<>0) c
where 数量 >= c.number

仅供参考
是否可以在此基础上根据条件拆分订单
比如说
8010-00422-0 富裕 档线 33 支 193页 2016-03-11 00:00:00.000 8311 h3463 NULL
8010-00422-0 富裕 上梁 5 支 193页 2016-03-11 00:00:00.000 8311 h3463 NULL


这个单子 拆分成：
8010-00422-0 富裕 档线 20 支 193页 2016-03-11 00:00:00.000 8311 h3463 t1
8010-00422-0 富裕 档线 13 支 193页 2016-03-11 00:00:00.000 8311 h3463 t2
8010-00422-0 富裕 上梁 5 支 193页 2016-03-11 00:00:00.000 8311 h3463 t2

把档线超过30的自动拆分成20和余数比如例子中的33拆分成了13和20 拆分后作出不同的标识t1和这个单号做区分
若同一单号数量超过30作出提示这个是不是相当复杂？怎么去做呢？？？跪求大神

[spiritofdragon]

with dataSource(订单编号,数量) as 
(
	select 'A001' ,33 union all
	select 'A002' ,5 union all
	select 'A003' ,160
)
,c as (
	SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/20 FullRow,数量%20 ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,20 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

[yiminglian]

引用 1 楼 spiritofdragon 的回复:

with dataSource(订单编号,数量) as 
(
	select 'A001' ,33 union all
	select 'A002' ,5 union all
	select 'A003' ,160
)
,c as (
	SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/20 FullRow,数量%20 ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,20 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

这个实例可以通用么？比如我数量变成35他就分成30和15

[yiminglian]

引用 1 楼 spiritofdragon 的回复:

with dataSource(订单编号,数量) as 
(
	select 'A001' ,33 union all
	select 'A002' ,5 union all
	select 'A003' ,160
)
,c as (
	SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/20 FullRow,数量%20 ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,20 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

引用 1 楼 spiritofdragon 的回复:

with dataSource(订单编号,数量) as 
(
	select 'A001' ,33 union all
	select 'A002' ,5 union all
	select 'A003' ,160
)
,c as (
	SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/20 FullRow,数量%20 ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,20 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

测试了一下，缺少了条件档线那个。单号应该是自动提取的 如select 订单编号，数量小计 from 数据源 where 档线>30 上面的代码使用了递归。我没测试成功

[spiritofdragon]

declare @FullRowSize int=30;
with dataSource(订单编号,数量) as 
(
    select 'A001' ,33 union all
    select 'A002' ,5 union all
    select 'A003' ,60
)
,c as (
    SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/@FullRowSize FullRow,数量%@FullRowSize ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,@FullRowSize 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

[yiminglian]

引用 4 楼 spiritofdragon 的回复:

declare @FullRowSize int=30;
with dataSource(订单编号,数量) as 
(
    select 'A001' ,33 union all
    select 'A002' ,5 union all
    select 'A003' ,60
)
,c as (
    SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/@FullRowSize FullRow,数量%@FullRowSize ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,@FullRowSize 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

请教为啥两台机器一台可以运行无错误一台提示： 消息 139，级别 15，状态 1，第 0 行 不能向局部变量赋予默认值。 消息 137，级别 15，状态 2，第 12 行 必须声明标量变量 "@FullRowSize"。 消息 137，级别 15，状态 2，第 16 行 必须声明标量变量 "@FullRowSize"。 消息 102，级别 15，状态 1，第 23 行 ')' 附近有语法错误。

[yiminglian]

引用 4 楼 spiritofdragon 的回复:

declare @FullRowSize int=30;
with dataSource(订单编号,数量) as 
(
    select 'A001' ,33 union all
    select 'A002' ,5 union all
    select 'A003' ,60
)
,c as (
    SELECT[number]+1 RowNum FROM [master].dbo.[spt_values] WHERE type='p'
)
,d1 as (
select *,数量/@FullRowSize FullRow,数量%@FullRowSize ModCnt
from dataSource
)
,d2 as (
select 订单编号,数量,@FullRowSize 分后数量, c.RowNum 分后序号
from d1,c
where d1.FullRow>=c.RowNum
union all
select 订单编号,数量,ModCnt ,FullRow+1
from d1
where ModCnt>0
)
select * from d2
order by 订单编号,分后序号

数据类型 float 和 int 在 modulo 运算符中不兼容。单号是文本 数量是int 为啥会出现这个错误呢