计算欠款的联合查询
表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
[解决办法]
--表CUSTOM
/*CustomNO CustomName PRICE
1111111 张三 李四 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
create table [CUSTOM]
(CustomNO varchar(10), CustomName varchar(10), PRICE int)
insert into CUSTOM
select '1111111', '张三 李四', 3000
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.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)
*/
