求助:求sql语句...

baidu_28619399 2015-05-31 09:50:32

想通过A,B,C的订单量，分配后面的数量，比如第一周 20，则优先分配A(订单量最大的),满足A后，B,C分配0,第二周50件,分配A剩余的30件，剩下20件分配给B，C为0.....

在线等...

...全文

115 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

习惯性蹭分 2015-05-31

打赏
举报



use tempdb

if object_id('orders') is not null
drop table orders
if object_id('orderDemand') is not null
drop table orderDemand
go
create table orders(orderID varchar(30),orderqty int)
insert into orders
select 'A',50 union all
select 'B',40 union all
select 'C',1

if object_id('result') is not null
drop table result
create table result(orderID varchar(30),订单 int,[第一周(20)] int,[第二周(20)] int,[第三周(20)] int)

declare @oid varchar(30),@qty int
declare @week1 int,@week2 int,@week3 int
select @week1=20,@week2=50,@week3=21
declare @tmpqty int,@tmpweek1 int,@tmpweek2 int,@tmpweek3 int

declare cur cursor for 
select orderID,orderqty from orders
open cur
fetch next from cur into @oid,@qty
set @tmpqty=0
while @@fetch_status=0
begin
 set @tmpqty=@tmpqty+@qty
 select  @tmpweek1=case when @tmpqty>=@week1 then @week1 else @tmpqty end,
         @tmpqty=@tmpqty-@tmpweek1,@week1=@week1-@tmpweek1
 select  @tmpweek2= case when @tmpqty>=@week2 then @week2 else  @tmpqty end,
         @tmpqty=@tmpqty-@tmpweek2,@week2=@week2-@tmpweek2
 select  @tmpweek3= case when @tmpqty>=@week3 then @week3 else  @tmpqty end,
         @tmpqty=@tmpqty-@tmpweek3,@week3=@week3-@tmpweek3
  insert into result       
 select @oid,@qty,@tmpweek1 ,@tmpweek2,@tmpweek3
fetch next from cur into @oid,@qty

end
close cur
deallocate cur

select * from result

baidu_28619399 2015-05-31

打赏
举报

我的周是动态的，最大是20周, 好吧，我这边数据库是sybase的，我以为语法都差不多，原谅我的无知.

还在加载中灬 2015-05-31

打赏
举报

楼主，你逗了
这里面没有用到自定义函数
把以下开始查询开始，下面的表名字段名替换对，可以直接放到程序里执行

我在最开始就说明了，有些东西，写出来你不一定懂或改得对

config_man 2015-05-31

打赏
举报

子陌红尘说：“怪就怪这几天下的雨”

baidu_28619399 2015-05-31

打赏
举报

怪我没说明白，我说的纯SQL是指单纯通过增删改查语句实现的，不使用函数，因为这个是要放在程序里的，只能通过SQL把值算好，放到临时表里.. 不好意思,怪我怪我..

还在加载中灬 2015-05-31

打赏
举报

IF OBJECT_ID('周安排表')IS NOT NULL

	DROP TABLE 周安排表

GO

;WITH [CTE]([周],[数量])AS(

	SELECT'1',20

	UNION ALL SELECT'2','50'

	UNION ALL SELECT'3','21'

)

SELECT [周],[数量] INTO 周安排表 FROM CTE

IF OBJECT_ID('订单表')IS NOT NULL

	DROP TABLE 订单表

GO

;WITH [CTE]([订单],[数量])AS(

	SELECT'A',50

	UNION ALL SELECT'B','40'

	UNION ALL SELECT'C','1'

)

SELECT [订单],[数量] INTO 订单表 FROM CTE

--以上模拟数据



--以下开始查询

;WITH T1 AS(

	SELECT ROW_NUMBER()OVER(ORDER BY 周)RN1,*

	FROM 周安排表

)

,T2 AS(

	SELECT ROW_NUMBER()OVER(ORDER BY 订单)RN2,*

	FROM 订单表

)

,CTE AS(

	SELECT T1.周,T2.订单

		,CASE WHEN T1.数量>T2.数量 THEN T2.数量 ELSE T1.数量 END 数量

		,T1.RN1,T2.RN2,T2.数量-T1.数量 [Balance]

	FROM T1 JOIN T2 ON T1.RN1=1 AND T2.RN2=1

	UNION ALL

	SELECT T1.周,T2.订单

		,CASE WHEN T1.数量>T2.[Balance] THEN T2.[Balance] ELSE T1.数量 END

		,T1.RN1,T2.RN2,T2.[Balance]-T1.数量

	FROM T1 JOIN CTE T2 ON T2.[Balance]>0 AND T1.RN1=T2.RN1+1

	UNION ALL

	SELECT T1.周,T2.订单

		,CASE WHEN -T1.[Balance]>T2.数量 THEN T2.数量 ELSE -T1.[Balance] END

		,T1.RN1,T2.RN2,T2.数量+T1.[Balance]

	FROM CTE T1 JOIN T2 ON T1.[Balance]<0 AND T2.RN2=T1.RN2+1

	UNION ALL

	SELECT T1.周,T2.订单

		,CASE WHEN T1.数量>T2.数量 THEN T2.数量 ELSE T1.数量 END

		,T1.RN1,T2.RN2,T2.数量-T1.数量

	FROM CTE T0 JOIN T1 ON T0.[Balance]=0 AND T1.RN1=T0.RN1+1

		JOIN T2 ON T2.RN2=T0.RN2+1

)

SELECT 周,订单,数量 FROM CTE/*如果需要行转列，则注释这一句

SELECT 订单

	,SUM(CASE WHEN 周=1 THEN 数量 ELSE 0 END)[第1周]

	,SUM(CASE WHEN 周=2 THEN 数量 ELSE 0 END)[第2周]

	,SUM(CASE WHEN 周=3 THEN 数量 ELSE 0 END)[第3周]

FROM CTE

GROUP BY 订单

--*/