create view Customer as
select
a.customer_name,
a.customer_account,
customer_money = (select sum(customer_money) from CustomerCapi where customer_account=a.customer_account),
customer_fee = (select sum(customer_fee) from CustomerCons where customer_account=a.customer_account)
customer_balance = (select sum(customer_money) from CustomerCapi where customer_account=a.customer_account) - (select sum(customer_fee) from CustomerCons where customer_account=a.customer_account)
from
customer_id a
select
a.customer_name,
a.customer_account,
customer_money = (select sum(customer_money) from CustomerCapi where customer_account=a.customer_account),
customer_fee = (select sum(customer_fee) from CustomerCons where customer_account=a.customer_account)
customer_balance = (select sum(customer_money) from CustomerCapi where customer_account=a.customer_account) - (select sum(customer_fee) from CustomerCons where customer_account=a.customer_account)
from
customer_id a
b.customer_money-c.customer_fee as customer_balance
from customer a
left join (
select customer_account,sum(customer_money) as customer_money from CustomerCapi
group by customer_account ) on a.customer_account=b.customer_account
left join (
select customer_account,sum(customer_fee) as customer_fee from CustomerCons
group by customer_account) c on a.customer_account=c.customer_account
AS
select min(customer_name), A.customer_account, sum(C.customer_money) as customer_money,
sum(B.customer_fee) as customer_fee,
sum(C.customer_money)- sum(B.customer_fee) as customer_balance
from customer A inner join CustomerCons B on A.customer_account=B.customer_account
inner join CustomerCapi C on A.customer_account=B.customer_account
group by A.customer_account
create view name
as
select customer_id,customer_name,a.customer_account,b.customer_money,c.customer_fee,
b.customer_money-c.customer_fee as customer_balance
from customer a
left join (
select customer_account,sum(customer_money) as customer_money from CustomerCapi
group by customer_account ) on a.customer_account=b.customer_account
left join (
select customer_account,sum(customer_fee) as customer_fee from CustomerCons
group by customer_account) c on a.customer_account=c.customer_account
from customer c1 join CustomerCapi c2
on c1.customer_account=c2.customer_account
join CustomerCons c3
on c2.customer_account=c3.customer_account
group by customer_account,customer_name
create table customer(customer_id int,customer_name varchar(10),customer_account varchar(10))
insert into customer select 1,'小王','xiaowang'
insert into customer select 2,'小张','xiaozhang'
insert into customer select 3,'小李','xiaoli'create table CustomerCapi(customercapi_id int,customer_account varchar(10),customer_money int)
insert into CustomerCapi select 1,'xiaowang',50
insert into CustomerCapi select 2,'xiaowang',70
insert into CustomerCapi select 3,'xiaowang',100
insert into CustomerCapi select 4,'xiaozhang',75
insert into CustomerCapi select 5,'xiaozhang',64
insert into CustomerCapi select 6,'xiaozhang',50
create table CustomerCons(customercons_id int,customer_account varchar(10),customer_fee int)
insert into CustomerCons select 1,'xiaowang',10
insert into CustomerCons select 2,'xiaowang',20
insert into CustomerCons select 3,'xiaowang',10
insert into CustomerCons select 4,'xiaozhang',20
insert into CustomerCons select 5,'xiaozhang',20
insert into CustomerCons select 6,'xiaozhang',20
--创建视图
create view Customer1 as
select
a.customer_name,
a.customer_account,
customer_money = (select isnull(sum(customer_money),0) from CustomerCapi where customer_account=a.customer_account),
customer_fee = (select isnull(sum(customer_fee),0) from CustomerCons where customer_account=a.customer_account),
customer_balance = (select isnull(sum(customer_money),0) from CustomerCapi where customer_account=a.customer_account) - (select isnull(sum(customer_fee),0) from CustomerCons where customer_account=a.customer_account)
from
customer a
--检索视图
select * from Customer1
--输出结果
customer_name customer_account customer_money customer_fee customer_balance
------------- ---------------- -------------- ------------- ----------------
小王 xiaowang 220 40 180
小张 xiaozhang 189 60 129
小李 xiaoli 0 0 0