关于先进先出的SQL语句(分配问题)

ttllhh 2017-05-14 08:00:31
可以理解为先进先出的问题,如下:
有个表:TB1
表结构和数据如下:

客户号 销售日期  销售额
001 2017-05-01 460
001 2017-05-02 240
001 2017-05-03 300


第二个表:TB2
表结构和数据如下:
客户号 付款额
001 500

现在想得到如下结果:
客户号 销售日期 销售额 实付额
001 2017-05-01 460 460
001 2017-05-02 240 40
001 2017-05-03 300 0


这样的SQL应该怎么写?在线等,非常感谢!!!


...全文
759 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2017-05-22
  • 打赏
  • 举报
回复
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
declare @TB1 table([客户号] nvarchar(23),[销售日期] Date,[销售额] int)
Insert @TB1
select N'001','2017-05-01',460 union all
select N'001','2017-05-02',240 union all
select N'001','2017-05-03',300
 

--> --> 中国风(Roy)生成測試數據
 
declare @TB2 table([客户号] nvarchar(23),[付款额] int)
Insert @TB2
select N'001',500

;WITH CTETB
AS
( 
SELECT  a.*,SUM(a.[销售额])OVER(PARTITION BY a.[客户号] ORDER BY a.[销售日期] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Sum销售额,ISNULL([付款额],0) AS [付款额]
FROM    @TB1 AS a
        LEFT JOIN ( SELECT  [客户号] ,
                            SUM([付款额]) AS [付款额]
                    FROM    @TB2
                    GROUP BY [客户号]
                  ) AS b ON a.[客户号] = b.[客户号]
)
SELECT  [客户号] ,
        [销售日期] ,
        [销售额] ,
        CASE WHEN Sum销售额 < [付款额] THEN [销售额]
             WHEN Sum销售额 < [付款额] + [销售额] THEN [付款额] - ( Sum销售额 - [销售额] )
             ELSE 0
        END AS 实付额
FROM    CTETB;
/*
客户号	销售日期	销售额	实付额
001	2017-05-01	460	460
001	2017-05-02	240	40
001	2017-05-03	300	0
*/
等待戈多12 2017-05-22
  • 打赏
  • 举报
回复
可能你只是举例中是这样,但我还是顺便提醒一下,结果中应该取的是剩余库存,而不是入库数量。
等待戈多12 2017-05-22
  • 打赏
  • 举报
回复
测试数据用一楼的,这里不再贴了。

--考虑到不确定历次的入库数量要减几次才减满销售数量,实际中历次入库数量也不相同,因此用到游标
declare @salenum int
set @salenum=500   --销售数量
declare @innum int
declare @indate date

declare xrxc_cursor cursor 
for
select 销售日期, 销售额 
from #tb1
where 客户号='001'
order by 销售日期

open xrxc_cursor

fetch next from xrxc_cursor into @indate, @innum

while @salenum > 0
begin
  
  select 客户号,销售日期, @innum 销售额, 
  (case when @innum < @salenum then @innum else @salenum end) as 实付额 from #tb1
  where 销售日期=@indate
  and 销售额=@innum

  set @salenum=@salenum-@innum
 
fetch next from xrxc_cursor into @indate, @innum
end

close xrxc_cursor
deallocate xrxc_cursor

查询结果如下,可能和你期望的略有差别。
gw6328 2017-05-15
  • 打赏
  • 举报
回复

DECLARE @t TABLE(id INT ,d DATETIME,m int);

INSERT INTO @t VALUES 
(1,'2017-05-01',460)
,(1,'2017-05-02',240)
,(1,'2017-05-03',300)

DECLARE @t1 TABLE(id INT,m int);
INSERT INTO @t1 SELECT 1,500;


;WITH cte AS(
	SELECT *,rn=ROW_NUMBER() OVER(ORDER BY d) FROM @t
), cx AS (
SELECT *,(SELECT SUM(m) FROM cte WHERE rn<=a.rn) c,(SELECT SUM(m) FROM cte WHERE rn<a.rn) AS cx FROM cte a 
)
SELECT CASE WHEN b.m>a.c THEN a.m ELSE iif(b.m-a.cx>0,b.m-a.cx,0) END AS mm,a.*,b.m AS mn FROM cx a JOIN @t1 b ON a.id=b.id

/*
mm          id          d                       m           rn                   c           cx          mn
----------- ----------- ----------------------- ----------- -------------------- ----------- ----------- -----------
460         1           2017-05-01 00:00:00.000 460         1                    460         NULL        500
40          1           2017-05-02 00:00:00.000 240         2                    700         460         500
0           1           2017-05-03 00:00:00.000 300         3                    1000        700         500
*/
道素 2017-05-15
  • 打赏
  • 举报
回复
不知道下面的能否满足需求,主要是考虑tb2 表如果多上,你希望怎么处理,我这里是汇总算的,如果你需要tb1 分配tb2每一笔都对应出,需要换一种写法

;with tb1(客户号,销售日期,销售额)AS(
    select '001','2017-05-01',460 union all
    select '001','2017-05-02',240 union  all
    select '001','2017-05-03',300 
),TB2(客户号,付款额)AS(
    select  '001',500
)
select t1.客户号,t1.销售日期,t1.销售额 ,case when t2.付款额<=isnull(tt1.p_销售额,0) then 0 else 
                case when (isnull(p_销售额,0)+t1.销售额)>=t2.付款额 then t2.付款额-isnull(p_销售额,0) else t1.销售额 end 
          end as 实付额
from tb1 as t1
cross apply (select sum(付款额) as 付款额 from tb2 where tb2.客户号=t1.客户号) as t2
outer apply(select sum(销售额) as p_销售额 from tb1 as tt where tt.客户号=t1.客户号 and tt.销售日期<t1.销售日期) as tt1


edit mode |  history
  	客户号	销售日期	销售额	实付额
1	001	2017-05-01	460	460
2	001	2017-05-02	240	40
3	001	2017-05-03	300	0

xiaoxiangqing 2017-05-14
  • 打赏
  • 举报
回复
楼上的不错.
二月十六 版主 2017-05-14
  • 打赏
  • 举报
回复
--测试数据
if not object_id(N'Tempdb..#TB1') is null
drop table #TB1
Go
Create table #TB1([客户号] nvarchar(23),[销售日期] Date,[销售额] int)
Insert #TB1
select N'001','2017-05-01',460 union all
select N'001','2017-05-02',240 union all
select N'001','2017-05-03',300
GO
if not object_id(N'Tempdb..#TB2') is null
drop table #TB2
Go
Create table #TB2([客户号] nvarchar(23),[付款额] int)
Insert #TB2
select N'001',500
Go
--测试数据结束
;WITH temp AS (
SELECT * ,
CASE WHEN ( SELECT SUM(付款额)
FROM #TB2
WHERE #TB1.客户号 = #TB2.客户号
) - 销售额 > 0 THEN 销售额
ELSE ( SELECT SUM(付款额)
FROM #TB2
WHERE #TB1.客户号 = #TB2.客户号
)
END AS 实付额 ,
( SELECT SUM(付款额)
FROM #TB2
WHERE #TB1.客户号 = #TB2.客户号
) - 销售额 AS 剩余额
FROM #TB1
WHERE 销售日期 = '2017-05-01'
UNION ALL
SELECT a.* ,
CASE WHEN b.剩余额 - a.销售额 > 0 THEN a.销售额
WHEN b.剩余额 > 0 THEN b.剩余额
ELSE 0
END AS 实付额 ,
b.剩余额 - a.销售额 AS 剩余额
FROM #TB1 a
JOIN temp b ON b.客户号 = a.客户号
AND DATEDIFF(DAY, b.销售日期, a.销售日期) = 1
WHERE b.实付额 >= 0
)
SELECT temp.客户号,temp.销售日期,temp.销售额,temp.实付额 FROM temp



34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧