订单查询未开发票余额的问题（多对多计算查询）

[mqmmx]

已知发票和订单表及发票与订单关连表，想得到 订单还有多个发票金额没有开，要怎么写sql
一个订单可能有多个发票，一个发票可能也对应多个订单， 小订单优先匹配发票的原则

订单号 发票号
1 2
1 3
2 3
2 4
4 5


订单号 （主键） 订单金额
1 200
2 400
3 500
4 100

发票号( 主键） 发票金额
2 100
3 200
4 120
5 100

得到的结果应该 是
订单号 金额 未收发票金额
1 200 0 （ 200 - 100 -100 ）
2 400 180 （ 400 - 200 -120 - 100）
3 500 500
4 100 0

[二月十六]

--测试数据
if not object_id(N'Tempdb..#关连表') is null
	drop table #关连表
Go
Create table #关连表([订单号] int,[发票号] int)
Insert #关连表
select 1,2 union all
select 1,3 union all
select 2,3 union all
select 2,4 union all
select 4,5
GO
if not object_id(N'Tempdb..#订单表') is null
	drop table #订单表
Go
Create table #订单表([订单号] int,[订单金额] int)
Insert #订单表
select 1,200 union all
select 2,400 union all
select 3,500 union all
select 4,100
GO
if not object_id(N'Tempdb..#发票表') is null
	drop table #发票表
Go
Create table #发票表([发票号] int,[发票金额] int)
Insert #发票表
select 2,100 union all
select 3,200 union all
select 4,120 union all
select 5,100
Go
--测试数据结束
SELECT
    *,
    (CASE
         WHEN (订单金额 >= ISNULL(
                           (
                               SELECT
                                   SUM(发票金额)
                               FROM
                                   #发票表
                                   JOIN
                                       #关连表
                                           ON #关连表.发票号 = #发票表.发票号
                               WHERE
                                   订单号 = #订单表.订单号
                           ), 0
                             )
              )
             THEN (订单金额 - ISNULL(
                              (
                                  SELECT
                                      SUM(发票金额)
                                  FROM
                                      #发票表
                                      JOIN
                                          #关连表
                                              ON #关连表.发票号 = #发票表.发票号
                                  WHERE
                                      订单号 = #订单表.订单号
                              ), 0
                                )
                  )
         ELSE
             0
     END
    ) AS 未收发票金额
FROM
    #订单表;

[mqmmx]

不对啊， 订单2 应该 有 180 没有开， 不是80 啊， 发票3 中 有100 是1号订单的， 只有100是2号订单的。

你这样直接汇总是不行的。

[k3rlt]

你这个条件给的不对啊，没有体现出来发票3中有100是1号订单的

[k3rlt]

。我看错了，我再研究下

[k3rlt]

初始化：
CREATE table ttt1( orderid int(11), billid int(11));
CREATE table ttt2( orderid int(11), order_money int(11));
CREATE table ttt3( billid int(11), bill_money int(11));
insert into ttt1 values(1,2),(1,3),(2,3),(2,4),(4,5);
insert into ttt2 values(1,200),(2,400),(3,500),(4,100);
insert into ttt3 values(2,100),(3,200),(4,120),(5,100);

执行sql：
select u1.orderid,u2.order_money totalMoney,(u2.order_money-u1.money) leftMoney from (select r.orderid,sum(r.oo) money from (select p.orderid,p.billid,p.order_money,p.bill_money,
(CASE p.cnum WHEN 1 THEN
p.bill_money
ELSE
(CASE p.onum WHEN 2 THEN
(CASE greatest(p.order_money,p.snum) WHEN p.snum THEN
p.bill_money - (SELECT o3.bill_money FROM ttt1 o1,ttt3 o3 where o1.billid = o3.billid and o1.orderid = p.orderid and o1.billid <> p.billid)
ELSE
(
/**存在条件不足的情况**/
select n.order_money-n.oo from (select p.orderid,p.billid,p.order_money,p.bill_money,
(CASE p.cnum WHEN 1 THEN
p.bill_money
ELSE
(CASE p.onum WHEN 2 THEN
(CASE greatest(p.order_money,p.snum) WHEN p.snum THEN
p.bill_money - (SELECT o3.bill_money FROM ttt1 o1,ttt3 o3 where o1.billid = o3.billid and o1.orderid = p.orderid and o1.billid <> p.billid)
ELSE
0
END)
ELSE /* 条件不足 */
0
END)
END) oo
from
(SELECT t1.orderid,t1.billid,t2.order_money,t3.bill_money,(select count(*) from ttt1 n1 where n1.billid=t1.billid) cnum,(select count(*) FROM ttt1 m1 WHERE m1.orderid = t1.orderid) onum ,(SELECT SUM(y3.bill_money) from ttt1 y1,ttt3 y3 where y1.billid = y3.billid AND y1.orderid = t1.orderid group by y1.orderid) snum
from ttt1 t1 left join ttt2 t2 on t1.orderid = t2.orderid LEFT JOIN ttt3 t3 on t3.billid = t1.billid) p) n WHERE n.orderid <> p.orderid and n.billid = p.billid
/****/
)
END)
ELSE /* 条件不足 */
0
END)
END) oo
from
(SELECT t1.orderid,t1.billid,t2.order_money,t3.bill_money,(select count(*) from ttt1 n1 where n1.billid=t1.billid) cnum,(select count(*) FROM ttt1 m1 WHERE m1.orderid = t1.orderid) onum ,(SELECT SUM(y3.bill_money) from ttt1 y1,ttt3 y3 where y1.billid = y3.billid AND y1.orderid = t1.orderid group by y1.orderid) snum
from ttt1 t1 left join ttt2 t2 on t1.orderid = t2.orderid LEFT JOIN ttt3 t3 on t3.billid = t1.billid) p) r group by r.orderid) u1, ttt2 u2 WHERE u1.orderid = u2.orderid

执行结果截图：

[hyqsweety]

CREATE table tbl_order_bill( orderid int, billid int);
CREATE table tbl_order( orderid int, order_money int);
CREATE table tbl_bill( billid int, bill_money int);
insert into tbl_order_bill values(1,2),(1,3),(2,3),(2,4),(4,5);
insert into tbl_order values(1,200),(2,400),(3,500),(4,100);
insert into tbl_bill values(2,100),(3,200),(4,120),(5,100);

with temp as (
select
order_rn=ROW_NUMBER()over(order by a.order_money,c.billid)
,bill_rn=ROW_NUMBER()over(order by c.billid)
,a.orderid,a.order_money,b.billid,c.bill_money
from tbl_order a
left join tbl_order_bill b on a.orderid=b.orderid
left join tbl_bill c on c.billid=b.billid
), temp_result as (

select *,use_bill=case when order_money- (select sum(bill_money) from temp b where a.orderid=b.orderid and a.order_rn>=b.order_rn )>0
then bill_money
else order_money -((select sum(bill_money) from temp b where a.orderid=b.orderid and a.order_rn>=b.order_rn )-bill_money) end
from temp a
), result as (

select *
,actual_use_bill=case when bill_money- (select sum(use_bill) from temp_result c where c.billid=a.billid and a.bill_rn>=c.bill_rn)>0
then use_bill
else bill_money -((select sum(use_bill) from temp_result c where c.billid=a.billid and a.bill_rn>=c.bill_rn)-use_bill) end
from temp_result a

)

select orderid,order_money,order_money-ISNULL(sum(actual_use_bill),0)
from result
group by orderid,order_money
order by orderid