表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 行受影响)