求SQL: 3表联合查询统计
表a：

ID NAME MONEY
1 车费 30
1 仓储费 500
2 搬运费 300

表b：

ID ITEM ACCOUNT
1 服务费 200
1 仓储费 500

表c:
ID Name
1 远航
2 英华


得到如下结果：

ID 客户 费用名称 应收 成本
1 远航 服务费 200 0
仓储费 500 500
车费 0 30

合计毛利： 170


ID 客户 费用名称 应收 成本
2 英华 搬运费 0 300

合计毛利： -300


[解决办法]
select * from c inner join (select Id=(case when a.id=null then b.id else a.id end),Name=(case when a.Name=null then b.Item else a.Name end),Money=sum(a.Money),Account=Sum(b.Account) from a full outer join b a.id=b.id group by ID,Name) cc on c.id=cc.id
[解决办法]

SQL code

--> 测试数据: #Aif object_id('tempdb.dbo.#A') is not null drop table #Acreate table #A (ID int,NAME varchar(6),MONEY int)insert into #Aselect 1,'车费',30 union allselect 1,'仓储费',500 union allselect 2,'搬运费',300--> 测试数据: #Bif object_id('tempdb.dbo.#B') is not null drop table #Bcreate table #B (ID int,ITEM varchar(6),ACCOUNT int)insert into #Bselect 1,'服务费',200 union allselect 1,'仓储费',500--> 测试数据: #Cif object_id('tempdb.dbo.#C') is not null drop table #Ccreate table #C (ID int,Name varchar(4))insert into #Cselect 1,'远航' union allselect 2,'英华'SELECT C.*,T.* FROM (SELECT ID,NAME,SUM(应收)应收,SUM(成本) 成本, 0 AS RN  FROM (SELECT ID ,NAME,0 AS 应收,MONEY AS 成本  FROM #AUNION ALLSELECT ID,ITEM,ACCOUNT,0 FROM #B) T GROUP BY ID,NAMEUNION ALLSELECT ID,'合计毛利',0, SUM(应收)-SUM(成本) AS 合计毛利 ,1 AS RN FROM (SELECT ID ,NAME,0 AS 应收,MONEY AS 成本  FROM #AUNION ALLSELECT ID,ITEM,ACCOUNT,0 FROM #B) T GROUP BY ID) T,#C C WHERE T.ID=C.IDORDER BY T.ID,T.RN/*（所影响的行数为 3 行）（所影响的行数为 2 行）（所影响的行数为 2 行）ID          Name ID          NAME     应收          成本          RN          ----------- ---- ----------- -------- ----------- ----------- ----------- 1           远航   1           仓储费      500         500         01           远航   1           车费       0           30          01           远航   1           服务费      200         0           01           远航   1           合计毛利     0           170         12           英华   2           搬运费      0           300         02           英华   2           合计毛利     0           -300        1（所影响的行数为 6 行）
[解决办法]
SQL code
select c.id,c.name,tb.name,tb.money from c left join (select id,name,money from a union all select id,item,account from b) tb on c.id=tb.id
[解决办法]
SQL code
---测试数据---if object_id('[a]') is not null drop table [a]gocreate table [a]([ID] int,[NAME] varchar(6),[MONEY] int)insert [a]select 1,'车费',30 union allselect 1,'仓储费',500 union allselect 2,'搬运费',300if object_id('[b]') is not null drop table [b]gocreate table [b]([ID] int,[ITEM] varchar(6),[ACCOUNT] int)insert [b]select 1,'服务费',200 union allselect 1,'仓储费',500if object_id('[c]') is not null drop table [c]gocreate table [c]([ID] int,[Name] varchar(4))insert [c]select 1,'远航' union allselect 2,'英华' ---查询---select   case when rn=1 then ltrim(ID) else '' end as ID,  case when rn=1 then 客户 else '' end as 客户,  费用名称,  应收,  成本from(select c.id,c.name as 客户,b.费用名称,应收=sum(应收),成本=sum(成本),rn=row_number() over(partition by c.id order by getdate())from cleft join(  select id,name as 费用名称,[money] as 应收,0 as 成本 from a   union all   select id,ITEM,0,[ACCOUNT] from b) bon c.id=b.idgroup by c.id,c.name,b.费用名称) t---结果---ID           客户   费用名称   应收          成本------------ ---- ------ ----------- -----------1            远航   仓储费    500         500                  车费     30          0                  服务费    0           2002            英华   搬运费    300         0(4 行受影响) 
 
[解决办法]
SQL code
--建立测试环境IF OBJECT_ID('a') IS NOT NULL  DROP TABLE aGOCREATE TABLE a(    id int ,     item varchar(10),    account int     )GOINSERT aSELECT 1 ,'车费', 30 union allSELECT 1 ,'仓储费', 500 union allSELECT 2 ,'搬运费', 300goIF OBJECT_ID('b') IS NOT NULL  DROP TABLE bGOCREATE TABLE b(    id int ,     item varchar(10),    account int     )GOINSERT bSELECT 1 ,'服务费', 200 union allSELECT 1 ,'仓储费', 500 goIF OBJECT_ID('c') IS NOT NULL  DROP TABLE cGOCREATE TABLE c(    id int ,     name varchar(10))GOINSERT cSELECT 1 ,'远航' union allSELECT 2 ,'英华'--查询select *  from(select isnull(t1.ID,t2.id) id,isnull(t1.Name,t2.name) 客户, isnull(a.item,b.item)费用名称,isnull(b.account,0)应收 ,isnull(a.account,0)成本from (c t1 left join b on t1.id=b.id )full join( c t2 left join a on t2.id=a.id)on t1.id=t2.id and a.item=b.itemunion allselect isnull(t1.ID,t2.id) id,'合计毛利：',ltrim(sum(isnull(b.account,0))-sum(isnull(a.account,0))),null,nullfrom (c t1 left join b on t1.id=b.id )full join( c t2 left join a on t2.id=a.id)on t1.id=t2.id and a.item=b.itemgroup by isnull(t1.ID,t2.id)) torder by 1,case when 客户='合计毛利：' then 1 else 0 end--结果/*id          客户         费用名称         应收          成本----------- ---------- ------------ ----------- -----------1           远航         车费           0           301           远航         仓储费          500         5001           远航         服务费          200         01           合计毛利：      170          NULL        NULL2           英华         NULL         0           02           英华         搬运费          0           3002           合计毛利：      -300         NULL        NULL(7 行受影响)*/