求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
[解决办法]
--> 测试数据: #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 行）
[解决办法]
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
[解决办法]
---测试数据---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 行受影响)
[解决办法]
--建立测试环境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 行受影响)*/