表格的交叉格求和，SQL语法怎么解决

[kingsjava]

表一：timetable
timea
----------------------------
2019-07-1
2019-07-2
2019-07-3
..........
2019-07-31
----------------------------

表二：typea
typetit typeid
--------------
伙食费 1
加油费 2
工资 3
出差费 4
.......
活动费 20
---------------------------------

表三：feiyong
id timea typeid jiner
-------------------------------------
1 2019-07-02 1 200
2 2019-07-01 1 100
3 2019-07-02 2 500
4 2019-07-01 1 300
........................
10 2019-07-05 4 100
------------------------------------------------------

想要的表格如下：

支出日期 伙食费 加油费 工资 出差费 。。。 xxxx费 合计
-----------------------------------------------------------------------------------
2019-07-01 400
2019-07-02 200 500
...................................
2019-07-31........
----------------------------------------------------------------------------------
合计：

[ManBOyyy]

引用 3 楼 听雨停了 的回复:

--测试数据
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'timetable') is null
	drop table timetable
Go
Create table timetable([timea] Date)
Insert timetable
select '2019-07-1' union all
select '2019-07-2' union all
select '2019-07-3' union ALL
select '2019-07-5' union all
select '2019-07-31'
Go
Select * from timetable

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'typea') is null
	drop table typea
Go
Create table typea([typetit] nvarchar(23),[typeid] int)
Insert typea
select N'伙食费',1 union all
select N'加油费',2 union all
select N'工资',3 union all
select N'出差费',4 union all
select N'活动费',20
Go
Select * from typea

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'feiyong') is null
	drop table feiyong
Go
Create table feiyong([id] int,[timea] Date,[typeid] int,[jiner] int)
Insert feiyong
select 1,'2019-07-02',1,200 union all
select 2,'2019-07-01',1,100 union all
select 3,'2019-07-02',2,500 union all
select 4,'2019-07-01',1,300 union all
select 10,'2019-07-05',4,100
Go
Select * from feiyong
--测试数据结束

--合计数据后插入到#tab表中
if not object_id(N'#tab') is null
	drop table #tab
Go
;WITH cte AS (
	SELECT t.timea,t2.typetit,f.jiner FROM timetable AS t
	LEFT JOIN feiyong AS f ON t.timea=f.timea
	LEFT JOIN typea AS t2 ON f.typeid=t2.typeid
)
SELECT timea,typetit,SUM(ISNULL(jiner,0)) AS jiner 
INTO #tab --插入#tab
FROM cte
GROUP BY timea,typetit
UNION ALL
SELECT timea,'总合计',SUM(isnull(jiner,0)) AS jiner FROM cte
GROUP BY timea

--行转列以及合计最后一行的数据
DECLARE @name VARCHAR(max),@sql VARCHAR(max),@sumname VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[typetit]+']'  from #tab for xml PATH('')),1,1,'')
set @sumname =stuff((SELECT DISTINCT ',sum(['+[typetit]+'])'  from #tab for xml PATH('')),1,1,'')
set @sql ='
SELECT convert(varchar,timea) timea,'+@name+' from #tab pivot(max(jiner)for typetit in('+@name+'))a 
union all
select ''合计'','+@sumname+' from (
	SELECT * from #tab pivot(max(jiner)for typetit in('+@name+'))a
) b 
'
PRINT @sql
EXEC( @sql);

DROP TABLE #tab;

楼上已经给出了行转列的博客地址，这个东西还是要自己去看去学习的。没有什么东西是天生就会的，这个东西其实你去认真看一下你会发现其实也就那么回事，没你想的那么复杂。

厲害，還這麼有空啊

[正怒月神]

行转列。 给你个例子 动态和静态的方式，你自己选择 https://blog.csdn.net/hanjun0612/article/details/56673854

[kingsjava]

能不能帮忙写下，不会写，求

[听雨停了]

--测试数据
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'timetable') is null
	drop table timetable
Go
Create table timetable([timea] Date)
Insert timetable
select '2019-07-1' union all
select '2019-07-2' union all
select '2019-07-3' union ALL
select '2019-07-5' union all
select '2019-07-31'
Go
Select * from timetable

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'typea') is null
	drop table typea
Go
Create table typea([typetit] nvarchar(23),[typeid] int)
Insert typea
select N'伙食费',1 union all
select N'加油费',2 union all
select N'工资',3 union all
select N'出差费',4 union all
select N'活动费',20
Go
Select * from typea

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'feiyong') is null
	drop table feiyong
Go
Create table feiyong([id] int,[timea] Date,[typeid] int,[jiner] int)
Insert feiyong
select 1,'2019-07-02',1,200 union all
select 2,'2019-07-01',1,100 union all
select 3,'2019-07-02',2,500 union all
select 4,'2019-07-01',1,300 union all
select 10,'2019-07-05',4,100
Go
Select * from feiyong
--测试数据结束

--合计数据后插入到#tab表中
if not object_id(N'#tab') is null
	drop table #tab
Go
;WITH cte AS (
	SELECT t.timea,t2.typetit,f.jiner FROM timetable AS t
	LEFT JOIN feiyong AS f ON t.timea=f.timea
	LEFT JOIN typea AS t2 ON f.typeid=t2.typeid
)
SELECT timea,typetit,SUM(ISNULL(jiner,0)) AS jiner 
INTO #tab --插入#tab
FROM cte
GROUP BY timea,typetit
UNION ALL
SELECT timea,'总合计',SUM(isnull(jiner,0)) AS jiner FROM cte
GROUP BY timea

--行转列以及合计最后一行的数据
DECLARE @name VARCHAR(max),@sql VARCHAR(max),@sumname VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[typetit]+']'  from #tab for xml PATH('')),1,1,'')
set @sumname =stuff((SELECT DISTINCT ',sum(['+[typetit]+'])'  from #tab for xml PATH('')),1,1,'')
set @sql ='
SELECT convert(varchar,timea) timea,'+@name+' from #tab pivot(max(jiner)for typetit in('+@name+'))a 
union all
select ''合计'','+@sumname+' from (
	SELECT * from #tab pivot(max(jiner)for typetit in('+@name+'))a
) b 
'
PRINT @sql
EXEC( @sql);

DROP TABLE #tab;

楼上已经给出了行转列的博客地址，这个东西还是要自己去看去学习的。没有什么东西是天生就会的，这个东西其实你去认真看一下你会发现其实也就那么回事，没你想的那么复杂。