一个很绕的SQL需求：一个合同可以被多个发货关联，一个合同也可以被多个发票关联；但是发货和发票没关联。要求通过“匀”的机制，让合同、发货、发票都关联起来

Question

合同明细和发货明细有对应
fhtid finvcode ffhid fcount
101 P78 501 30
101 P78 502 13
101 P80 501 17
101 P80 502 4
102 P78 501 19
102 P78 502 1

合同明细和发票明细有对应
fhtid finvcode ffpid ffpc
101 P78 301 16
101 P78 302 10
101 P80 301 28
101 P80 302 0
102 P78 NULL 0
102 P80 NULL 0
102 P90 NULL 0

如何把合同明细和发货明细与发票明细 也对应起来？
对应的规则是“匀”，先把靠前（按fid排序）的记录对应满，有多才对应下一条，不够则有多少算多少
希望的结果如下：
fhtid finvcode ffhid fcount ffpid ffpc
101 P78 501 30 301 16 \不够则有16算16
101 P78 501 30 302 10 \不够则有10算10
101 P78 501 30 缺 4 301、302两个发票里的P78加起来还少4(=16+10 - 30)
101 P78 502 13 缺 13 还是P78，上面已经把16+10都用完了，所以它就完全缺了
101 P80 501 17 301 17 \30>17，所以满17
101 P80 502 4 301 4 满4，301的P80还多出7(=28-17-4)
102 P78 501 19 缺 19 \
102 P78 502 1 缺 1 这2个发货对应的合同没有此货物的发票，所以全部缺了

同一个货物（finvcode相同）的前提下
一个合同可以被多个发货关联，一个合同也可以被多个发票关联；但是发货和发票没关联
要求通过“匀”的机制，让合同、发货、发票都关联起来想了好几天了。。
生成例子数据的sql：使用表变量，临时表都不用
--合同表
declare @am table(fid int,fno varchar(50))
--合同明细表
declare @ai table(fid int,fmainid int,finvcode varchar(50),fcount int)--发货表
declare @dm table(fid int,fno varchar(50))
--发货明细表
declare @di table(fid int,fmainid int,finvcode varchar(50),fcount int)--合同明细-发货明细 对应表
declare @x table(fid int,fhtiid int,ffhiid int,fcount int)--发票表（其中fhtid为对应的合同表id，靠主记录对应，不再有明细对应表）
declare @bm table(fid int,fno varchar(50),fhtid int)
--发票明细表
declare @bi table(fid int,fmainid int,finvcode varchar(50),fcount int)
insert @am select 101,'HT1'
insert @ai select 201,101,'P78',50
union all select 202,101,'P80',60
insert @am select 102,'HT2'
insert @ai select 203,102,'P78',80
union all select 204,102,'P80',45
union all select 205,102,'P90',77insert @bm select 301,'HT1-1',101
insert @bi select 401,301,'P78',16
union all select 402,301,'P80',20
insert @bm select 302,'HT1-2',101
insert @bi select 403,302,'P78',10
union all select 404,301,'P80',8insert @dm select 501,'FH1'
insert @di select 601,501,'P78',75
union all select 602,501,'P80',6
insert @dm select 502,'FH2'
insert @di select 603,502,'P78',15
union all select 604,502,'P80',20insert @x select 701,201,601,10
union all select 702,201,603,13
union all select 703,201,601,20
union all select 704,202,602,7
union all select 704,202,604,4
union all select 704,202,602,10
union all select 705,203,601,15
union all select 706,203,601,4
union all select 707,203,603,1--with tx as
--(
--select fhtiid,ffhiid,sum(fcount) fcount
--from @x
--group by fhtiid,ffhiid
--)
--select* from tx
--这个语句居然报错：
--明明非递归的cte是可以使用group by的。
select ai.fmainid fhtid,ai.finvcode,di.fmainid ffhid,sum(x.fcount) fcount
from (
select fhtiid,ffhiid,sum(fcount) fcount
from @x
group by fhtiid,ffhiid
) x
left join @ai ai on x.fhtiid=ai.fid
left join @di di on x.ffhiid=di.fid
group by ai.fmainid,ai.finvcode,di.fmainid
order by ai.fmainid,ai.finvcode,di.fmainidselect ai.fmainid fhtid,ai.finvcode,bm.fid ffpid --,sum(ai.fcount) fhtc
,sum(isnull(bi.fcount,0)) ffpc
from @ai ai
left join @bm bm on ai.fmainid=bm.fhtid
left join @bi bi on bi.fmainid=bm.fid and bi.finvcode=ai.finvcode
group by ai.fmainid,ai.finvcode,bm.fid

Answer 1

101    P80    501    17    301    17    \30>17，所以满17
应该是
101    P80    501    17    301    17    \28>17，所以满17否则误导了

Answer 2

;with tx as --前面要加分号。

Answer 3

--想不出什么巧妙的办法，用循环写了：--合同表
declare @am table(fid int,fno varchar(50))
--合同明细表
declare @ai table(fid int,fmainid int,finvcode varchar(50),fcount int)--发货表
declare @dm table(fid int,fno varchar(50))
--发货明细表
declare @di table(fid int,fmainid int,finvcode varchar(50),fcount int)--合同明细-发货明细 对应表
declare @x table(fid int,fhtiid int,ffhiid int,fcount int)--发票表（其中fhtid为对应的合同表id，靠主记录对应，不再有明细对应表）
declare @bm table(fid int,fno varchar(50),fhtid int)
--发票明细表
declare @bi table(fid int,fmainid int,finvcode varchar(50),fcount int)
insert @am select 101,'HT1'
insert @ai select 201,101,'P78',50
union all select 202,101,'P80',60
insert @am select 102,'HT2'
insert @ai select 203,102,'P78',80
union all select 204,102,'P80',45
union all select 205,102,'P90',77insert @bm select 301,'HT1-1',101
insert @bi select 401,301,'P78',16
union all select 402,301,'P80',20
insert @bm select 302,'HT1-2',101
insert @bi select 403,302,'P78',10
union all select 404,301,'P80',8insert @dm select 501,'FH1'
insert @di select 601,501,'P78',75
union all select 602,501,'P80',6
insert @dm select 502,'FH2'
insert @di select 603,502,'P78',15
union all select 604,502,'P80',20insert @x select 701,201,601,10
union all select 702,201,603,13
union all select 703,201,601,20
union all select 704,202,602,7
union all select 704,202,604,4
union all select 704,202,602,10
union all select 705,203,601,15
union all select 706,203,601,4
union all select 707,203,603,1--with tx as
--(
--select fhtiid,ffhiid,sum(fcount) fcount
--from @x
--group by fhtiid,ffhiid
--)
--select* from tx
--这个语句居然报错：
--明明非递归的cte是可以使用group by的。
select ai.fmainid fhtid,ai.finvcode,di.fmainid ffhid,sum(x.fcount) fcount,
ROW_NUMBER() OVER (order by ai.fmainid,ai.finvcode,di.fmainid) AS SEQ
INTO #1
from (
select fhtiid,ffhiid,sum(fcount) fcount
from @x
group by fhtiid,ffhiid
) x
left join @ai ai on x.fhtiid=ai.fid
left join @di di on x.ffhiid=di.fid
group by ai.fmainid,ai.finvcode,di.fmainid
order by ai.fmainid,ai.finvcode,di.fmainidselect ai.fmainid fhtid,ai.finvcode,bm.fid ffpid --,sum(ai.fcount) fhtc
,sum(isnull(bi.fcount,0)) ffpc,
ROW_NUMBER() OVER (order by ai.fmainid,ai.finvcode,bm.fid) AS SEQ
INTO #2
from @ai ai
left join @bm bm on ai.fmainid=bm.fhtid
left join @bi bi on bi.fmainid=bm.fid and bi.finvcode=ai.finvcode
group by ai.fmainid,ai.finvcode,bm.fidDECLARE @TB TABLE(fhtid INT,    finvcode VARCHAR(3),    ffhid  INT,  fcount  INT,  ffpid  INT,  ffpc INT, SEQ INT)   
DECLARE @TEMP TABLE(ffpc INT, nextseq INT)
DECLARE @fhtid INT, @finvcode VARCHAR(3), @ffhid  INT,    @fcount INT, @ffpid    INT, @SEQ INT,
@SEQ2 INT, @count INT
SET @SEQ=1WHILE EXISTS(SELECT * FROM #1 WHERE SEQ=@SEQ)
BEGIN
SELECT @fhtid=fhtid,  @finvcode=finvcode, @ffhid=ffhid, @fcount=fcount, @count=fcount FROM #1 WHERE SEQ=@SEQ

WHILE @@ROWCOUNT>0
BEGIN 
SET @count=@count-ISNULL((SELECT ffpc FROM @TEMP), 0)
SET @SEQ2=(SELECT nextseq FROM @TEMP)+1
DELETE @TEMP INSERT @TB
OUTPUT INSERTED.ffpc, INSERTED.SEQ INTO @TEMP
SELECT TOP 1 @fhtid, @finvcode, @ffhid, @fcount,ffpid,
CASE WHEN @count>#2.ffpc THEN #2.ffpc ELSE @count END,#2.SEQ
FROM #2 LEFT JOIN (SELECT SEQ,SUM(ffpc) AS ffpc FROM @TB GROUP BY SEQ) AS T
ON #2.SEQ=T.SEQ
WHERE fhtid=@fhtid AND finvcode=@finvcode AND #2.ffpc>ISNULL(T.ffpc,0) AND #2.SEQ=ISNULL(@SEQ2,#2.SEQ)
ORDER BY #2.SEQ
END IF @count>0
INSERT @TB SELECT @fhtid, @finvcode, @ffhid, @fcount,NULL,@count,0 SET @SEQ=@SEQ+1
ENDSELECT fhtid,finvcode ,ffhid,fcount ,ISNULL(CAST(ffpid AS VARCHAR), N'缺') ffpid,ffpc 
FROM @TBDROP TABLE #1
DROP TABLE #2
/*
fhtid       finvcode ffhid       fcount      ffpid                          ffpc
----------- -------- ----------- ----------- ------------------------------ -----------
101         P78      501         30          301                            16
101         P78      501         30          302                            10
101         P78      501         30          缺                              4
101         P78      502         13          缺                              13
101         P80      501         17          301                            17
101         P80      502         4           301                            4
102         P78      501         19          缺                              19
102         P78      502         1           缺                              1
*/

Answer 4

公用表达式可以 group :
;with tx as(
select fhtiid,ffhiid,sum(fcount) fcount
from @x
group by fhtiid,ffhiid
),t1 as(
select top 100 ai.fmainid fhtid,ai.finvcode,di.fmainid ffhid,sum(tx.fcount) fcount,
    ROW_NUMBER() OVER (order by ai.fmainid,ai.finvcode,di.fmainid) AS SEQ
from tx
left join @ai ai on tx.fhtiid=ai.fid
left join @di di on tx.ffhiid=di.fid
group by ai.fmainid,ai.finvcode,di.fmainid
order by ai.fmainid,ai.finvcode,di.fmainid
)select * from t1
/*
fhtid       finvcode                                           ffhid       fcount      SEQ
----------- -------------------------------------------------- ----------- ----------- --------------------
101         P78                                                501         30          1
101         P78                                                502         13          2
101         P80                                                501         17          3
101         P80                                                502         4           4
102         P78                                                501         19          5
102         P78                                                502         1           6(6 行受影响)
*/

Answer 5

测测结果是否正确set nocount on;
--合同表
declare @am table(fid int,fno varchar(50))
--合同明细表
declare @ai table(fid int,fmainid int,finvcode varchar(50),fcount int)--发货表
declare @dm table(fid int,fno varchar(50))
--发货明细表
declare @di table(fid int,fmainid int,finvcode varchar(50),fcount int)--合同明细-发货明细 对应表
declare @x table(fid int,fhtiid int,ffhiid int,fcount int)--发票表（其中fhtid为对应的合同表id，靠主记录对应，不再有明细对应表）
declare @bm table(fid int,fno varchar(50),fhtid int)
--发票明细表
declare @bi table(fid int,fmainid int,finvcode varchar(50),fcount int)
insert @am select 101,'HT1'insert @ai select 201,101,'P78',50
union all select 202,101,'P80',60
insert @am select 102,'HT2'
insert @ai select 203,102,'P78',80
union all select 204,102,'P80',45
union all select 205,102,'P90',77insert @bm select 301,'HT1-1',101
insert @bi select 401,301,'P78',16
union all select 402,301,'P80',20
insert @bm select 302,'HT1-2',101
insert @bi select 403,302,'P78',10
union all select 404,301,'P80',8insert @dm select 501,'FH1'
insert @di select 601,501,'P78',75
union all select 602,501,'P80',6
insert @dm select 502,'FH2'
insert @di select 603,502,'P78',15
union all select 604,502,'P80',20insert @x select 701,201,601,10
union all select 702,201,603,13
union all select 703,201,601,20
union all select 704,202,602,7
union all select 704,202,604,4
union all select 704,202,602,10
union all select 705,203,601,15
union all select 706,203,601,4
union all select 707,203,603,1;with a
as
(
select ai.fmainid fhtid,ai.finvcode,di.fid,di.fmainid ffhid,sum(x.fcount) fcount
from (
select fhtiid,ffhiid,sum(fcount) fcount
from @x
group by fhtiid,ffhiid
) x
inner join @ai ai on x.fhtiid=ai.fid
inner join @di di on x.ffhiid=di.fid
group by ai.fmainid,ai.finvcode,di.fmainid,di.fid
),a2 as
(select *,[sumFcount]=(select SUM(fcount) from a where fhtid=b.fhtid and finvcode=b.finvcode and fid<=b.fid) from a as b )
,b as
(
select b.fmainid,a.fhtid,b.fid,b.finvcode,fcount from @bm as a inner join @bi as b on a.fid=b.fmainid
),b2
as
(
select *,
[sumFcount]=(select SUM(fcount) from b where fhtid=a.fhtid and finvcode=a.finvcode and fid<=a.fid) from b as a
)
select 
a.fhtid,a.finvcode,a.ffhid,a.fcount,
rtrim(b.fmainid) as ffpid,
ffpc=sum(case when  a.[sumFcount]>b.[sumFcount] then b.[sumFcount] else a.[sumFcount] end-
case when b.[sumFcount]-b.fcount>a.[sumFcount]-a.fcount then b.[sumFcount]-b.fcount else a.[sumFcount]-a.fcount  end)
from a2 as a,b2 as b
where a.fhtid=b.fhtid and a.finvcode=b.finvcode and a.[sumFcount]>b.[sumFcount]-b.fcount
and b.[sumFcount]>a.[sumFcount]-a.fcount
group by a.fhtid,a.finvcode,a.ffhid,a.fcount,b.fmainid
union all
select  
a.fhtid,a.finvcode,a.ffhid,a.fcount,
N'缺' as ffpid,
[ffpc]=case when (a.sumFcount-a.fcount)>c.sumFcount or c.fhtid IS null then a.fcount else a.sumFcount-c.sumFcount end
from a2 as a
left join (select fhtid,finvcode,sumFcount=SUM(fcount) from b2 group by fhtid,finvcode) as b on  a.fhtid=b.fhtid and a.finvcode=b.finvcode and a.sumFcount<b.sumFcount
left join (select fhtid,finvcode,sumFcount=SUM(fcount) from b2 group by fhtid,finvcode) as c on  a.fhtid=c.fhtid and c.finvcode=a.finvcode and a.sumFcount>c.sumFcount
where b.fhtid is nullorder by 1,2,3/*
fhtid finvcode ffhid fcount ffpid ffpc
101 P78 501 30 301 16
101 P78 501 30 302 10
101 P78 501 30 缺 4
101 P78 502 13 缺 13
101 P80 501 17 301 17
101 P80 502 4 301 4
102 P78 501 19 缺 19
102 P78 502 1 缺 1
*/

Answer 6

结果是参照希望的结果如下：
fhtid    finvcode    ffhid    fcount    ffpid    ffpc    
101    P78    501    30    301    16    \不够则有16算16
101    P78    501    30    302    10    \不够则有10算10
101    P78    501    30    缺    4    301、302两个发票里的P78加起来还少4(=16+10 - 30)
101    P78    502    13    缺    13    还是P78，上面已经把16+10都用完了，所以它就完全缺了
101    P80    501    17    301    17    \30>17，所以满17
101    P80    502    4    301    4    满4，301的P80还多出7(=28-17-4)
102    P78    501    19    缺    19    \
102    P78    502    1    缺    1    这2个发货对应的合同没有此货物的发票，所以全部缺了

Answer 7

厉害！使用union all补记录、使用“>”关联来“匀”！
在“匀”的时候不需要考虑发票号的时候，我也这样已经实现了单边的“匀”
但是需求要求增加发票号时，我真的不知道如何左边、右边一起“匀”了。可能需要更多的例子记录，才能验证各种情况
你的sql我再慢慢分析、体会先谢谢了！！
合同、发货、发票 3者的全关联问题
合同、发货有明细对应，每笔关联其中的部分，同一合同、发货的同一产品，也可以关联多次，只要累计数量不超过此合同-此产品的实际数和此发货-此产品的实际数
而发票则仅仅与合同主表关联，是多（发票）对一（合同）的关系，明细是自动依据产品对应实际要的3个报表：
1、发货号-产品-发货数--关联的合同号-（产品相同）-（累计多次关联）关联数--发票号-（产品相同）-（匀得的）发票数
2、合同号-产品-合同数--关联的发货号-（产品相同）-（累计多次关联）关联数--发票号-（产品相同）-（匀得的）发票数
3、发票号-产品-发票数--关联的合同号-（产品相同）-合同数--关联的发货号-（产品相同）-（累计多次关联）关联数这次提的的报表2
如果合同数>（累计多次关联）发货关联数，需要补一条 无发货号的记录，关联数为 未关联的数
其它报表也类似

Answer 8

这个需求，报表2可以理解为这样的过程：
n个桌子（合同：合同号+产品），每张桌子(Hi)上按顺序摆有mi个不同容量的空碗(发货)，和pi个不同容量的装满酒的壶(发票)
要求把每张桌子上的壶里的酒按顺序倒到本桌的碗里，逐个倒满（靠前的壶先用，倒完了再用靠后的壶；靠前的碗先被倒，倒满了再轮到靠后的碗）
因为每个发票是仅仅属于一个合同的，所以，壶不需要跨桌的倒（不需要倒给别的桌子的碗）
结果是：桌子(合同号+产品)-合同数(相当于一个总容量hx>=累计的fx)--碗(发货号)-容量(fx)-酒的体积(fy)--哪个壶倒的(发票号)-给本碗倒了多少(fz)
如果hx>=累计的fx，则空增一条，碗号为null，fx=hx-累计的fx
如果fx>累计的fy，则空增一条，壶号为null，fy=fx-累计的fy

Answer 9

好像501的P80关联17不对，应该501的P80本身只有6！
这个属于元素数据矛盾了
另外，union all select 404,301,'P80',8 --这里302误写为301了，301前面已经有P80了如果能列出完整的列，就容易发现问题：
合同号-产品-合同数--关联的发货号-（产品相同）-（累计多次关联）关联数--发票号-（产品相同）-（匀得的）发票数

Answer 10

set nocount on;
--合同表
declare @am table(fid int,fno varchar(50))
--合同明细表
declare @ai table(fid int,fmainid int,finvcode varchar(50),fcount int)--发货表
declare @dm table(fid int,fno varchar(50))
--发货明细表
declare @di table(fid int,fmainid int,finvcode varchar(50),fcount int)--合同明细-发货明细 对应表
declare @x table(fid int,fhtiid int,ffhiid int,fcount int)--发票表（其中fhtid为对应的合同表id，靠主记录对应，不再有明细对应表）
declare @bm table(fid int,fno varchar(50),fhtid int)
--发票明细表
declare @bi table(fid int,fmainid int,finvcode varchar(50),fcount int)
insert @am select 101,'HT1'insert @ai select 201,101,'P78',50
union all select 202,101,'P80',60
insert @am select 102,'HT2'
insert @ai select 203,102,'P78',80
union all select 204,102,'P80',45
union all select 205,102,'P90',77insert @bm select 301,'HT1-1',101
insert @bi select 401,301,'P78',16
union all select 402,301,'P80',20
insert @bm select 302,'HT1-2',101
insert @bi select 403,302,'P78',10
union all select 404,302,'P80',8 --原来 302误写为301了insert @dm select 501,'FH1'
insert @di select 601,501,'P78',75
union all select 602,501,'P80',66  --此次只有6，所以改为66
insert @dm select 502,'FH2'
insert @di select 603,502,'P78',15
union all select 604,502,'P80',20insert @x select 701,201,601,10
union all select 702,201,603,13
union all select 703,201,601,20
union all select 704,202,602,7  --
union all select 704,202,604,4
union all select 704,202,602,10  --7+10=17>6了，不应该。所以上面的6改为66了
--此表数据本来是程序界面根据手工选择自动生成，有相应的限制。我制作数据时，没核对这个限制
union all select 705,203,601,15
你sql的结果是：
fhtid finvcode ffhid fcount ffpid ffpc
101 P78 501 30 301 16
101 P78 501 30 302 10
101 P78 501 30 缺 4
101 P78 502 13 缺 13
101 P80 501 17 301 17
101 P80 502 4 301 3
101 P80 502 4 302 1
102 P78 501 15 缺 15
希望更详细的结果是：（最好把明细记录的id也列出来，以便查阅）
fhtid finvcode 合同数 ffhid 发货数 关联数 ffpid 发票数 匀得
101 P78 50 501 75 30 301 16 16
101 P78 50 501 75 30 302 10
101 P78 50 501 75 30 缺发票 4
101 P78 50 502 15 13 缺发票 13
101 P78 50 尚未关联 0 7
101 P80 60 501 66 17 301 17
101 P80 60 502 66 4 301 3
101 P80 60 502 20 4 302 1
101 P80 60 尚未关联 0 39
102 P78 80 501 15 15 缺发票 15
102 P78 80 尚未关联 0 65
如果合同数=关联数，则不会有“尚未关联”行

Answer 11

上面希望更详细的结果是：（发票数漏填了。。）
fhtid finvcode 合同数 ffhid 发货数 关联数 ffpid 发票数 匀得
101 P78 50 501 75 30 301 16 16
101 P78 50 501 75 30 302 10 10
101 P78 50 501 75 30 缺发票 4
101 P78 50 502 15 13 缺发票 13
101 P78 50 尚未关联 0 7
101 P80 60 501 66 17 301 20 17
101 P80 60 502 66 4 301 20 3
101 P80 60 502 20 4 302 8 1
101 P80 60 尚未关联 0 39
102 P78 80 501 15 15 缺发票 15
102 P78 80 尚未关联 0 65
如果合同数=关联数，则不会有“尚未关联”行

Answer 12

合同上是否少了兩條在結果集里
fid fno fid fmainid finvcode fcount
102 HT2 204 102 P80 45
102 HT2 205 102 P90 77

Answer 13

set nocount on;
--合同表
declare @am table(fid int,fno varchar(50))
--合同明细表
declare @ai table(fid int,fmainid int,finvcode varchar(50),fcount int)
--发货表
declare @dm table(fid int,fno varchar(50))
--发货明细表
declare @di table(fid int,fmainid int,finvcode varchar(50),fcount int)--合同明细-发货明细 对应表
declare @x table(fid int,fhtiid int,ffhiid int,fcount int)--发票表（其中fhtid为对应的合同表id，靠主记录对应，不再有明细对应表）
declare @bm table(fid int,fno varchar(50),fhtid int)
--发票明细表
declare @bi table(fid int,fmainid int,finvcode varchar(50),fcount int)
insert @am select 101,'HT1'insert @ai select 201,101,'P78',50
union all select 202,101,'P80',60
insert @am select 102,'HT2'
insert @ai select 203,102,'P78',80
union all select 204,102,'P80',45
union all select 205,102,'P90',77insert @bm select 301,'HT1-1',101
insert @bi select 401,301,'P78',16
union all select 402,301,'P80',20
insert @bm select 302,'HT1-2',101
insert @bi select 403,302,'P78',10
union all select 404,302,'P80',8 --原来 302误写为301了insert @dm select 501,'FH1'
insert @di select 601,501,'P78',75
union all select 602,501,'P80',66  --此次只有6，所以改为66
insert @dm select 502,'FH2'
insert @di select 603,502,'P78',15
union all select 604,502,'P80',20insert @x select 701,201,601,10
union all select 702,201,603,13
union all select 703,201,601,20
union all select 704,202,602,7  --
union all select 704,202,604,4
union all select 704,202,602,10  --7+10=17>6了，不应该。所以上面的6改为66了
--此表数据本来是程序界面根据手工选择自动生成，有相应的限制。我制作数据时，没核对这个限制
union all select 705,203,601,15;with a
as
(
select ai.fmainid fhtid,ai.finvcode,di.fid,di.fmainid ffhid,sum(x.fcount) fcount,ai.fcount AS 合同数,di.fcount AS  发货数
from (
select fhtiid,ffhiid,sum(fcount) fcount
from @x
group by fhtiid,ffhiid
) x
inner join @ai ai on x.fhtiid=ai.fid
inner join @di di on x.ffhiid=di.fid
group by ai.fmainid,ai.finvcode,di.fmainid,di.fid,ai.fcount,di.fcount
),a2 as
(select *,[sumFcount]=(select SUM(fcount) from a where fhtid=b.fhtid and finvcode=b.finvcode and fid<=b.fid) from a as b )
,b as
(
select b.fmainid,a.fhtid,b.fid,b.finvcode,fcount from @bm as a inner join @bi as b on a.fid=b.fmainid
),b2
as
(
select *,
[sumFcount]=(select SUM(fcount) from b where fhtid=a.fhtid and finvcode=a.finvcode and fid<=a.fid) from b as a
)
select 
    a.fhtid,a.finvcode,a.合同数,RTRIM(a.ffhid),a.发货数,a.fcount AS 关联数,RTRIM(b.fcount) AS 发票数,
    rtrim(b.fmainid) as ffpid,
    ffpc=RTRIM(sum(case when  a.[sumFcount]>b.[sumFcount] then b.[sumFcount] else a.[sumFcount] end-
    case when b.[sumFcount]-b.fcount>a.[sumFcount]-a.fcount then b.[sumFcount]-b.fcount else a.[sumFcount]-a.fcount  end))
from a2 as a,b2 as b
where a.fhtid=b.fhtid and a.finvcode=b.finvcode and a.[sumFcount]>b.[sumFcount]-b.fcount
and b.[sumFcount]>a.[sumFcount]-a.fcount
group by a.fhtid,a.finvcode,a.ffhid,a.fcount,b.fmainid,a.合同数,a.发货数,b.fcountunion all
select  
a.fhtid,a.finvcode,a.合同数,RTRIM(a.ffhid),a.发货数,a.fcount AS 关联数,发票数=N'缺发票',
N'缺' as ffpid,
[ffpc]=RTRIM(case when (a.sumFcount-a.fcount)>c.sumFcount or c.fhtid IS null then a.fcount else a.sumFcount-c.sumFcount END)
from a2 as a
left join (select fhtid,finvcode,sumFcount=SUM(fcount) from b2 group by fhtid,finvcode) as b on  a.fhtid=b.fhtid and a.finvcode=b.finvcode and a.sumFcount<b.sumFcount
left join (select fhtid,finvcode,sumFcount=SUM(fcount) from b2 group by fhtid,finvcode) as c on  a.fhtid=c.fhtid and c.finvcode=a.finvcode and a.sumFcount>c.sumFcount
where b.fhtid is null
UNION ALL 
SELECT 
b.fmainid fhtid,b.finvcode,b.fcount AS 合同数,ffhid=N'尚未关联',发货数=0,关联数=b.fcount-d.sumfcount,发票数='',ffpid='',[ffpc]=''
FROM @am AS a 
INNER JOIN @ai  AS b ON a.fid=b.fmainid
LEFT JOIN (select fhtiid,sum(fcount) sumfcount from @x group by fhtiid) AS c ON c.fhtiid=b.fid AND b.fcount<=c.sumfcount
inner JOIN (select fhtiid,sum(fcount) sumfcount from @x group by fhtiid) AS d ON d.fhtiid=b.fid AND b.fcount>d.sumfcount
WHERE c.fhtiid IS nullORDER BY 1,2,3,4
/*
fhtid finvcode 合同数 (沒有資料行名稱) 发货数 关联数 发票数 ffpid ffpc
101 P78 50 501 75 30 16 301 16
101 P78 50 501 75 30 10 302 10
101 P78 50 501 75 30 缺发票 缺 4
101 P78 50 502 15 13 缺发票 缺 13
101 P78 50 尚未关联 0 7
101 P80 60 501 66 17 20 301 17
101 P80 60 502 20 4 20 301 3
101 P80 60 502 20 4 8 302 1
101 P80 60 尚未关联 0 39
102 P78 80 501 75 15 缺发票 缺 15
102 P78 80 尚未关联 0 65
*/

Answer 14

看看以上結果是否正確,
速度慢時，把CTE生成臨時表

Answer 15

貼一下你1/3報表的顯示結果集，有時間給你看看。隨便提一下表名和字段命名很不規範，相同容易讓人混淆，花在這上面的時間>解決問題時間。
看文字你只需要把 union all后面兩個結果集改改，其它都是顯示格式的問題

Answer 16

1、3的结果集暂时不好凑，反正就是a/b/d三个原始表按从左到右的顺序，右边对左边的进行补充（有一对多的，就复制为多条记录，列明匀到的数量；没有对应的，就补一条null的记录，数量为缺的数）命名的确很要命
这次，主要是既有英文的首字母a/b/d，又有拼音的缩写ht合同agreement/fp发票bill/fh发货depature
主表就后跟一个m(main)，明细表就后跟一个i(item)对于你上面的sql，我还佩服除了sql的思路，还很惊讶能这么快能理解别人的各个字段。之前也觉得自己对sql的思维已经够接近，这次2边一起匀的实现，真的第一次感觉到无从下手
单边匀（只考虑每碗被倒了多少，不考虑是从哪些壶倒出来的）之前还勉强实现了