如何用一个SQL语句达到目的?(很有意思的问题)

Question

A.发货单
发货单号,序号,发货金额
------------------------------------
RA0245    1    1000.00
RA0245    2      500.00
RA0245    3    7000.00B.收款单
收款单号,发货单号,收款金额
--------------------------------------------
GB0411    RA0245    3500.00问题：
　　由于收款单是按发货单号收款的,但没有具体到发货序号.现在要计算业务员佣金,需要具体到发货序号,因为每个序号销售的产品或价格不同时,提成率会不一样.解决方案：
　　自动将收款金额按发货单号序号从小至大分配至发货序号中去，如以上例子可以这样，详情如下：发货单号,序号,发货金额,收款金额
--------------------------------------------------
RA0245    1    1000.00    1000.00
RA0245    2      500.00      500.00
RA0245    3    7000.00    2000.00问题：如何用一个SQL语句达到目的?

Answer 1

PT哥的--库存先进先出简单例子：create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j 
    else 
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
    end
    from t a where name=@name and j!=c 
end
else
    raiserror('库存不足',16,1)    
    return 
go
--测试：exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp

Answer 2

use Tempdb
go
--> --> 
 
declare @发货单 table([发货单号] nvarchar(6),[序号] int,[发货金额] decimal(18,2))
Insert @发货单
select N'RA0245',1,1000.00 union all
select N'RA0245',2,500.00 union all
select N'RA0245',3,7000.00
  
declare @收款单 table([收款单号] nvarchar(6),[发货单号] nvarchar(6),[收款金额] decimal(18,2))
Insert @收款单
select N'GB0411',N'RA0245',3500.00
 
select 
a.[发货单号],a.[序号],a.[发货金额],
[收款金额]=case when b.[收款金额]>=a.[金额] then [发货金额] when  b.[收款金额]>(a.金额-a.[发货金额]) then b.[收款金额]-(a.金额-a.[发货金额]) else 0 end
from 
(Select *,金额=(select sum([发货金额]) from @发货单 where [发货单号]=a.[发货单号] and [序号]<=a.[序号]) from @发货单 as a) as a
inner join 
@收款单 b on a.[发货单号]=b.[发货单号]发货单号   序号          发货金额                                    收款金额
------ ----------- --------------------------------------- ---------------------------------------
RA0245 1           1000.00                                 1000.00
RA0245 2           500.00                                  500.00
RA0245 3           7000.00                                 2000.00(3 個資料列受到影響)

Answer 3

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-12-17 14:05:28
-------------------------------------
 
--> 生成测试数据: @发货单
DECLARE @发货单 TABLE (发货单号 varchar(6),序号 int,发货金额 numeric(6,2))
INSERT INTO @发货单
SELECT 'RA0245',1,1000.00 UNION ALL
SELECT 'RA0245',2,500.00 UNION ALL
SELECT 'RA0245',3,7000.00
 
--> 生成测试数据: @收款单
DECLARE @收款单 TABLE (收款单号 varchar(6),发货单号 varchar(6),收款金额 numeric(6,2))
INSERT INTO @收款单
SELECT 'GB0411','RA0245',3500.00--SQL查询如下:SELECT
    A.*,
    CASE WHEN 发货金额 <B.收款金额-ISNULL( (SELECT SUM(发货金额)
                          FROM @发货单
                          WHERE 发货单号=A.发货单号
                              AND 序号<A.序号),0)
            THEN 发货金额
        ELSE
            CASE WHEN B.收款金额-ISNULL( (SELECT SUM(发货金额)
                          FROM @发货单
                          WHERE 发货单号=A.发货单号
                              AND 序号<A.序号),0) < 0
                    THEN 0
                 ELSE B.收款金额-ISNULL( (SELECT SUM(发货金额)
                          FROM @发货单
                          WHERE 发货单号=A.发货单号
                              AND 序号<A.序号),0)
            END
    END
FROM @发货单 AS A
    JOIN @收款单 AS B
ON A.发货单号=B.发货单号/*
发货单号   序号          发货金额                                    
------ ----------- --------------------------------------- ---------------------------------------
RA0245 1           1000.00                                 1000.00
RA0245 2           500.00                                  500.00
RA0245 3           7000.00                                 2000.00(3 行受影响)*/

Answer 4

本帖最后由 roy_88 于 2009-12-17 14:18:27 编辑