求货物分配的练习

预备数据:

--生成测试数据
drop table TBA,TBB
create table TBA(名称 varchar(10),数量 int,时间 varchar(10))
insert into TBA values('a',2,20071011) 

insert into TBA values('a',1,20071012) 
insert into TBA values('a',3,20071013) 

insert into TBA values('b',2,20071011) 
insert into TBA values('b',1,20071012)             
insert into TBA values('b',3,20071013) 

create table TBB(名称 varchar(10),数量 int)
insert into TBB values('a',3) 
insert into TBB values('a',1)
insert into TBB values('b',6)

希望看见的查询结果:

/**
名称   数量     时间      名称    已分配量
 a	     2	   20071011	   a	     2
 a	     1	   20071012	   a	     1
 a	     3	   20071013	   a	     1
 b	     2	   20071011	   b	     2
 b	     1	   20071012	   b	     1
 b	     3	   20071013	   b	     3
*/

答案：

------------测试语句
SELECT 名称, SUM(数量) from TBA WHERE TBA.时间<20071013 AND TBA.名称='a' GROUP BY TBA.名称

-----------解答一

SELECT   A.*,C.名称,C.总数,已分配量 = 
CASE WHEN   (C.总数-ISNULL((  SELECT SUM(D.数量) 
                              FROM TBA AS D 
                              WHERE D.时间<A.时间 
                              AND D.名称=A.名称 
                              GROUP BY D.名称),0))>=A.数量
     THEN 
  	     A.数量
     ELSE 
	    (C.总数-ISNULL(( SELECT SUM(D.数量) 
                             FROM TBA AS D 
                             WHERE D.时间<A.时间 
                             AND D.名称=A.名称 
                             GROUP BY D.名称),0))
     END
FROM TBA AS A
left JOIN 
	( SELECT B.名称 AS 名称,SUM(B.数量) AS 总数 
          FROM TBB B 
          GROUP BY B.名称) AS C 
ON ( A.名称=C.名称)


-----------解答二

SELECT TBA.*,TBB.数量-ISNULL((
                         SELECT SUM(数量) 
                         FROM TBA A 
                         WHERE A.名称 = TBA.名称 
                         AND A.时间< TBA.时间) ,0),
                       ISNULL(( SELECT SUM(数量) 
                                FROM TBA A 
                                WHERE A.名称 = TBA.名称 
                                AND A.时间< TBA.时间) ,0) 
                         as FCQ,TBB.数量 
FROM TBA 
LEFT OUTER JOIN 
           ( SELECT 名称,SUM(数量) AS 数量 
             FROM TBB 
             GROUP BY 名称 ) AS TBB  
             ON TBA.名称=TBB.名称