SQL2005, select * from ABcount(22,10)返回的结果是: 1 10 2 10 3 2select * from ABcount(10,6) 返回的是 1 6 2 4
--装箱关系表 declare @t table (AA varchar(10),num int) insert @t select 'A',30 union select 'A1',20 union select 'A2',25 declare @A varchar(10),@num int select @A='A2',@num=90--查询 select top 10000 fid=identity(int,1,1) into #t from dbo.sysobjects a,dbo.syscolumns bselect a.* from @t a,#t b where a.AA=@A and b.fid<=@num/a.num union all select AA,@num%num from @t where AA=@A and @num%num<>0drop table #t /* AA num ---------- ----------- A2 25 A2 25 A2 25 A2 15 */
create function F_Tab(@Acount int,@Bcount int) returns table as return( with C as (select 1 as n,case when @Acount>@Bcount then @Bcount else @Acount end as Acount union all select n+1 as n,case when @Acount-(n*@Bcount)> @Bcount then @Bcount else @Acount-(n*@Bcount) end from C where n<CEILING(@Acount*1.0/@Bcount)) select * from C ) go select * from F_Tab(10,6)select * from F_Tab(22,10) n Acount ----------- ----------- 1 6 2 4(2 行受影响)n Acount ----------- ----------- 1 10 2 10 3 2(3 行受影响)
谢谢12楼的,说实话,效率分析看不懂 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create FUNCTION [dbo].[ABcount] ( @Acount INT , @Bcount INT ) RETURNS @treeinfo table ( n INT NOT NULL, Bcount INT NULL ) AS BEGIN declare @countItemNo int; declare @c int declare @n int set @n=1 WHILE @Acount > 0 BEGIN if(@Acount>@Bcount) begin set @c=@Bcount; set @Acount=@Acount-@Bcount; end else begin set @c=@Acount; set @Acount=0; end INSERT INTO @treeinfo values(@n,@c) set @n=@n+1; END RETURN END select * from ABcount(22,10)这个也是对的,不知道哪个效率高,不过这个要循环插入 等再讨论讨论给你分
汗,比如A货物是24*24*24的规格,B货物是25*25*25的规格………… 容器是100*100*100的规格,那么就能计算出一个容器能对应的装多少种货物 现在货物来了,假如A能装20个,B能装15个对么,A来了100个,B来了100个 那么装法就是 A 容器1 20 A 容器2 20 A 容器3 20 A 容器4 20 A 容器5 20 B 容器6 15 B 容器7 15……………… 对么
create function F_Tab(@Acount int,@Bcount int) returns table as return( with C as (select 1 as n,case when @Acount>@Bcount then @Bcount else @Acount end as Acount union all select n+1 as n,case when @Acount-(n*@Bcount)> @Bcount then @Bcount else @Acount-(n*@Bcount) end from C where n<CEILING(@Acount*1.0/@Bcount)) select * from C ) go select * from F_Tab(10,6) select * from F_Tab(22,10)能不能批次处理啊,比如我输入了 A1 100, A2 80, A3 90, A4 96, A5 200 然后这个函数一起处理了,一条查询语句就是了,不要多条语句
select * from ABcount(22,10)返回的结果是:
1 10
2 10
3 2select * from ABcount(10,6)
返回的是
1 6
2 4
declare @t table (AA varchar(10),num int)
insert @t
select 'A',30 union
select 'A1',20 union
select 'A2',25 declare @A varchar(10),@num int
select @A='A2',@num=90--查询
select top 10000 fid=identity(int,1,1) into #t from dbo.sysobjects a,dbo.syscolumns bselect a.* from @t a,#t b where a.AA=@A and b.fid<=@num/a.num
union all
select AA,@num%num from @t where AA=@A and @num%num<>0drop table #t
/*
AA num
---------- -----------
A2 25
A2 25
A2 25
A2 15
*/
为什么是这种装法? 即依具或者规则是什么
A1 1 10
A1 2 10
A1 3 5
A2 4 20
………………
这样排下去
1,2,3,4是容器号,按顺序排就好
B中的规格是
A1 30一批
A2 20个一批
A3 25个一批而B的容量不限,是这个意思不?
create function F_Tab(@Acount int,@Bcount int)
returns table
as
return(
with C
as
(select 1 as n,case when @Acount>@Bcount then @Bcount else @Acount end as Acount
union all
select n+1 as n,case when @Acount-(n*@Bcount)> @Bcount then @Bcount else @Acount-(n*@Bcount) end
from C where n<CEILING(@Acount*1.0/@Bcount))
select * from C
)
go
select * from F_Tab(10,6)select * from F_Tab(22,10)
n Acount
----------- -----------
1 6
2 4(2 行受影响)n Acount
----------- -----------
1 10
2 10
3 2(3 行受影响)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[ABcount]
(
@Acount INT ,
@Bcount INT
)
RETURNS @treeinfo table
(
n INT NOT NULL,
Bcount INT NULL
)
AS
BEGIN
declare @countItemNo int;
declare @c int
declare @n int
set @n=1
WHILE @Acount > 0
BEGIN
if(@Acount>@Bcount)
begin
set @c=@Bcount;
set @Acount=@Acount-@Bcount;
end
else
begin
set @c=@Acount;
set @Acount=0;
end
INSERT INTO @treeinfo
values(@n,@c)
set @n=@n+1;
END RETURN END select * from ABcount(22,10)这个也是对的,不知道哪个效率高,不过这个要循环插入
等再讨论讨论给你分
容器是100*100*100的规格,那么就能计算出一个容器能对应的装多少种货物
现在货物来了,假如A能装20个,B能装15个对么,A来了100个,B来了100个
那么装法就是
A 容器1 20
A 容器2 20
A 容器3 20
A 容器4 20
A 容器5 20
B 容器6 15
B 容器7 15………………
对么
returns table
as
return(
with C
as
(select 1 as n,case when @Acount>@Bcount then @Bcount else @Acount end as Acount
union all
select n+1 as n,case when @Acount-(n*@Bcount)> @Bcount then @Bcount else @Acount-(n*@Bcount) end
from C where n<CEILING(@Acount*1.0/@Bcount))
select * from C
)
go
select * from F_Tab(10,6)
select * from F_Tab(22,10)能不能批次处理啊,比如我输入了
A1 100,
A2 80,
A3 90,
A4 96,
A5 200
然后这个函数一起处理了,一条查询语句就是了,不要多条语句