4 表联合查询求和问题,求 SQL ！
表a：
ID CODE REQUEST
1 A001 代理
2 B003 自营
表b：
CODE CNAME
A001 王刚
B003 张翼
表c：
ID DETAIL INVOICE
1 材料费 500
1 人工费 40
1 加班费 50
1 误工费 80
1 加急费 100
1 交通费 30
1 午餐费 10
1 晚餐费 12
。。
表d：
ID ACCOUNTNAME ACCOUNT
1 服务费 800
1 误工费 80
1 加急费 100
1 加班费 50
1 交通费 30
1 餐费 30
想根据CNAME查询，得到如下结果：
CNAME ID CODE REQUEST 应收 成本 毛利
王刚 1 A001 代理 860 592 268
应收要排除加班费、误工费、加急费，成本同样要排除加班费、误工费、加急费
[解决办法]
select b.cname,
a.id,
a.code,
a.requset,
c1.cnt as 应收,
d1.cnt as 成本,
(c1.cnt - d1.cnt) as 毛利
from a,
b,
(select id, sum(INVOICE) cnt
from (select id, detail, INVOICE
from c
where detail <> '加班费 ' and detail <> '误工费 ' and
detail <> '加急费 ')
group by id) c1,
(select id, sum(ACCOUNT) cnt
from (select id, ACCOUNTNAME, ACCOUNT
from d
where ACCOUNTNAME <> '加班费 ' and ACCOUNTNAME <> '误工费 ' and
ACCOUNTNAME <> '加急费 ')
group by id) d1
where a.code = b.code and a.id = c1.id and a.id = d1.id and
b.cname = '王刚 '
[解决办法]
select B.CName,A.ID,A.Code,A.REQUEST,(select sum(加减各字段) from C where ID=A.ID),(select sum (加减各字段) from D where ID=A.ID),(select sum(加减各字段) from C where ID=A.ID)- (select sum (加减各字段) from D where ID=A.ID),( from A inner join B on A.CODE=B.Code where CName= '王刚 '
------解决方案--------------------
SELECT CNAME , A.ID, A.CODE ,A.REQUEST, INVOICE AS 应收 ,ACCOUNT AS 成本 ,INVOICE-ACCOUNT AS 毛利
FROM B INNER JOIN A ON B.CODE=A.CODE
INNER JOIN (
select ID , sum(INVOICE) as INVOICE
from c
where DETAIL not in (N '加班费 ',N '误工费 ',N '加急费 ')
group by ID) E
ON A.ID=E.ID
INNER JOIN (
select ID , sum(ACCOUNT) as ACCOUNT
from d
where ACCOUNTNAME not in (N '加班费 ',N '误工费 ',N '加急费 ')
group by id ) F
ON A.ID=F.ID
[解决办法]
create table a (id int,code varchar(04),request varchar(10))
insert into a
select 1, 'A001 ', '代理 ' union all
select 2, 'B003 ', '自 '
create table b(code varchar(04),cname varchar(30))
insert into b
select 'A001 ', '王 ' union all
select 'B003 ', ' '
create table c(id int,detail varchar(30),invoice int)
insert into c
select 1, '材料 ',500 union all
select 1, '人工 ',40 union all
select 1, '加班 ',50 union all
select 1, '工 ',80 union all
select 1, '加急 ',100 union all
select 1, '交通 ',30 union all
select 1, '午餐 ',10 union all
select 1, '晚餐 ',12
create table d(id int,accountname varchar(30),account int)
insert into d
select 1, '服 ',800 union all
select 1, '工 ',80 union all
select 1, '加急 ',100 union all
select 1, '加班 ',50 union all
select 1, '交通 ',30 union all
select 1, '餐 ',30
Go
declare @cname varchar(30)
set @cname= '王 '
select T.cname,T.id,T.code,T.request,T.account,sum(c.invoice) as invoice,T.account-sum(c.invoice) as [毛利]
from
(select b.cname,a.id,a.code,a.request,sum(d.account) as account
from b,a,d
where b.cname=@cname
and b.code=a.code
and a.id=d.id
and d.accountname not in( '加班 ', '工 ', '加急 ')
group by b.cname,a.id,a.code,a.request) T, c
where T.id=c.id
and c.detail not in( '加班 ', '工 ', '加急 ')
group by T.cname,T.id,T.code,T.request,T.account
/*
cname id code request account invoice 毛利
------------------------------ ----------- ---- ---------- ----------- -----------
王 1 A001 代理 860 592 268
*/
drop table a,b,c,d
[解决办法]
create table a(ID int,CODE varchar(20),REQUEST varchar(20))
insert a select 1, 'A001 ', '代理 '
union all select 2, 'B003 ', '自营 '
create table b(CODE varchar(20),CNAME varchar(20))
insert b select 'A001 ', '王刚 '
union all select 'B003 ', '自营 '
create table c(ID int,DETAIL varchar(20),INVOICE int)
insert c select 1, '材料费 ',500
union all select 1, '人工费 ',40
union all select 1, '加班费 ',50
union all select 1, '误工费 ',80
union all select 1, '加急费 ',100
union all select 1, '交通费 ',30
union all select 1, '午餐费 ',10
union all select 1, '晚餐费 ',12
create table d(ID int,ACCOUNTNAME varchar(20),ACCOUNT int)
insert d select 1, '服务费 ',800
union all select 1, '误工费 ',80
union all select 1, '加急费 ',100
union all select 1, '加班费 ',50
union all select 1, '交通费 ',30
union all select 1, '餐费 ',30
select m.CNAME,m.ID,m.CODE,m.REQUEST,应收,成本,毛利=应收-成本 from
(
select CNAME,a.ID,a.CODE,REQUEST,
应收=sum(case when ACCOUNTNAME not in ( '误工费 ', '加急费 ', '加班费 ') then ACCOUNT end)
from a inner join b on a.CODE=b.CODE inner join d on a.ID=d.ID
group by CNAME,a.ID,a.CODE,REQUEST
)m
inner join
(
select CNAME,a.ID,a.CODE,REQUEST,
成本=sum(case when DETAIL not in ( '误工费 ', '加急费 ', '加班费 ') then INVOICE end)
from a inner join b on a.CODE=b.CODE inner join c on a.ID=c.ID
group by CNAME,a.ID,a.CODE,REQUEST
)n on m.CNAME=n.CNAME