关于先进先出的ＳＱＬ语句（分配问题）

Question

可以理解为先进先出的问题，如下：
有个表：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
这样的ＳＱＬ应该怎么写？在线等，非常感谢！！！

Answer 1

--测试数据
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

Answer 2

不知道下面的能否满足需求，主要是考虑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 tt1edit mode |  history
   客户号 销售日期 销售额 实付额
1 001 2017-05-01 460 460
2 001 2017-05-02 240 40
3 001 2017-05-03 300 0

Answer 3

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
*/

Answer 4

测试数据用一楼的，这里不再贴了。
--考虑到不确定历次的入库数量要减几次才减满销售数量，实际中历次入库数量也不相同，因此用到游标
declare @salenum int
set @salenum=500   --销售数量
declare @innum int
declare @indate datedeclare xrxc_cursor cursor 
for
select 销售日期, 销售额 
from #tb1
where 客户号='001'
order by 销售日期open xrxc_cursorfetch next from xrxc_cursor into @indate, @innumwhile @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
endclose xrxc_cursor
deallocate xrxc_cursor查询结果如下，可能和你期望的略有差别。

Answer 5

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
*/