计算欠款的联合查询解决思路
计算欠款的联合查询
表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
表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)