计算欠款的联合查询

Question

表CUSTOM
CustomNO CustomName PRICE
1111111  张三 李四     3000表MONEY
CustomNO Money
1111111  1000
1111111  2000
1111111  200表CustomDetials
CustomNO Sort Price
1111111  物品1 200
1111111  物品2 400如何得出结果为400

Answer 1

custom表中的3000是基本价格，money中是交款记录，customdetails是再消费项目。该例子的结果是。3000(custom)+200+400(customdetails)-2000-1000-200(money)=400

Answer 2

--表CUSTOM
/*CustomNO CustomName PRICE1111111  张三 李四     3000*/
Create Table CUSTOM
(
   CustomNO nvarchar(10),
   CustomName nvarchar(15),
   PRICE decimal
)
insert into CUSTOM
select '1111111','张三 李四',3000
union all 
select '1111112',' 王五',2000
/*
表MONEY
CustomNO Money
1111111  1000
1111111  2000
1111111  200*/
Create Table [MONEY]
(
   CustomNO nvarchar(10),
   [Money] decimal
   
)
insert into [MONEY]
select '1111111',1000
union all 
select '1111111',2000
union all 
select '1111111',200
union all 
select '1111112',300
union all 
select '1111112',400
/*表CustomDetials
CustomNO Sort Price
1111111  物品1 200
1111111  物品2 400 */
Create Table CustomDetials
(
   CustomNO nvarchar(10),
   Sort nvarchar(10),
   Price decimal
   
)
insert into CustomDetials
select '1111111','物品1',200
union all 
select '1111111','物品2',400
union all 
select '1111112','物品1',100
union all 
select '1111112','物品2',300
select (c.PRICE-m.sumMoney+cd.sumPrice) as Total from CUSTOM c,
(select m.CustomNO,sum(m.[Money]) sumMoney from [MONEY] m  group by m.CustomNO) as m,
(select cd.CustomNO,sum(cd.Price) sumPrice from CustomDetials cd  group by cd.CustomNO) as  cd
where c.CustomNO=m.CustomNO 
and c.CustomNO=cd.CustomNO

Answer 3

/*
Total
---------------------------------------
400
1700(2 行受影响)*/

Answer 4

老大正解，但是如果只显示结果不为0或Null的怎么加条件

Answer 5

create table [CUSTOM]
(CustomNO varchar(10), CustomName varchar(10), PRICE int)insert into CUSTOM
 select '1111111', '张三 李四', 3000create table [MONEY]
(CustomNO varchar(10), [Money] int)insert into [MONEY]
select '1111111', 1000 union all
select '1111111', 2000 union all
select '1111111', 200create table [CustomDetials]
(CustomNO varchar(10), Sort varchar(10), Price int)insert into [CustomDetials]
select '1111111', '物品1', 200 union all
select '1111111', '物品2', 400
select a.CustomNO,
       a.PRICE+b.Price-c.[Money] 'Price'
from
(select CustomNO,sum(PRICE) 'PRICE'
 from [CUSTOM] group by CustomNO) a
left join
(select CustomNO,sum(Price) 'Price'
 from [CustomDetials] group by CustomNO) b on a.CustomNO=b.CustomNO
left join
(select CustomNO,sum(Money) 'Money'
 from [MONEY] group by CustomNO) c on a.CustomNO=c.CustomNO
where a.PRICE+b.Price-c.[Money]<>0 and a.PRICE+b.Price-c.[Money] is not null
 
/*
CustomNO   Price
---------- -----------
1111111    400(1 row(s) affected)
*/

Answer 6

create table [CUSTOM]
(CustomNO varchar(10), CustomName varchar(10), PRICE int)insert into CUSTOM
 select '1111111', '张三 李四', 3000create table [MONEY]
(CustomNO varchar(10), [Money] int)insert into [MONEY]
select '1111111', 1000 union all
select '1111111', 2000 union all
select '1111111', 200create table [CustomDetials]
(CustomNO varchar(10), Sort varchar(10), Price int)insert into [CustomDetials]
select '1111111', '物品1', 200 union all
select '1111111', '物品2', 400
select a.CustomNO,
       a.PRICE+b.Price-c.[Money] 'Price'
from
(select CustomNO,sum(PRICE) 'PRICE'
 from [CUSTOM] group by CustomNO) a
left join
(select CustomNO,sum(Price) 'Price'
 from [CustomDetials] group by CustomNO) b on a.CustomNO=b.CustomNO
left join
(select CustomNO,sum(Money) 'Money'
 from [MONEY] group by CustomNO) c on a.CustomNO=c.CustomNO
where a.PRICE+b.Price-c.[Money]<>0 and a.PRICE+b.Price-c.[Money] is not null
 
/*
CustomNO   Price
---------- -----------
1111111    400(1 row(s) affected)
*/
这个也是正确的，要是再加一个列，怎么加呢。我怎么加不上？custom表中再加CustomName,Phone...怎么加到SQL里？

Answer 7

create table [CUSTOM]
(CustomNO varchar(10), CustomName varchar(10), PRICE int, Phone varchar(10))
 
insert into CUSTOM
 select '1111111', '张三 李四', 3000, '123456789'create table [MONEY]
(CustomNO varchar(10), [Money] int)
 
insert into [MONEY]
select '1111111', 1000 union all
select '1111111', 2000 union all
select '1111111', 200
 
create table [CustomDetials]
(CustomNO varchar(10), Sort varchar(10), Price int)
 
insert into [CustomDetials]
select '1111111', '物品1', 200 union all
select '1111111', '物品2', 400
 
 
select a.CustomNO,
       a.Phone,
       a.PRICE+b.Price-c.[Money] 'Price'
from
(select d.CustomNO,
        (select top 1 Phone 
         from CUSTOM e where e.CustomNO=d.CustomNO) 'Phone',
        sum(d.PRICE) 'PRICE'
 from [CUSTOM] d group by d.CustomNO) a
left join
(select CustomNO,sum(Price) 'Price'
 from [CustomDetials] group by CustomNO) b on a.CustomNO=b.CustomNO
left join
(select CustomNO,sum(Money) 'Money'
 from [MONEY] group by CustomNO) c on a.CustomNO=c.CustomNO
where a.PRICE+b.Price-c.[Money]<>0 and a.PRICE+b.Price-c.[Money] is not null/*
CustomNO   Phone      Price
---------- ---------- -----------
1111111    123456789  400(1 row(s) affected)
*/

Answer 8

又有一个新问题出来了。有的时候MONEY没有记寻，或CustomDetails表中没有记录就是NULL，这个时候计算的结果也是NULL，怎么在计算中把MONEY和CUSTOMDETAILS里的NULL值转换为0呢、

Answer 9

用Case when CUSTOMDETAILS is null then 0 else CUSTOMDETAILS end;

Answer 10

老大，你直接高估我的能力了，帮我加到前面的SQL里，我才看的懂。

Answer 11

用Case when CUSTOMDETAILS is null then 0 else CUSTOMDETAILS end;
怎么加到这个里面呢？
 (c.PRICE-m.sumMoney+cd.sumPrice) as Total

Answer 12

select a.customno,baseprice+ISNULL(againprice,0)-ISNULL([money],0) as 欠款 from (select customno,SUM(price) as baseprice from custom group by customno) as a
left join (select customno,SUM(price) as againprice from customdetails group by customno) as b
on a.customno=b.customno
left join (select customno,SUM(money)as [money] from [money] group by customno) as c
on a.customno=c.customno这个只适合你的有了基本消费才能有附加消费的情况，如果不是这种的custom与custondetail要先full join才行