34,591
社区成员
发帖
与我相关
我的任务
分享
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Name])+'=sum(case when [Name]='+quotename([Course],'''')+' then [free] else 0 end)'
from Type group by[Name]
exec('select 月份=Convert(char(7),Date,120)'+@s+' ,总计=sum(fee) from Type,free where type.id=free.typeid group by Convert(Char(7),Date,120)')
exec(@s)
SELECT CONVERT(char(7), Date, 120),SUM(CASE WHEN TypeID=1 THEN Fee ELSE 0 END)AS ONE,
SUM(CASE WHEN TypeID=2 THEN fee ELSE 0 END)AS TWO,SUM(Fee)
FROM fee表
GROUP BY CONVERT(char(7), Date, 120)
其它我不常用的日期格式转换方法:
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
DECLARE @s varchar(8000)
SELECT
@s = ''
SELECT
@s = @s + ',' + QUOTENAME(Name)
+ '=SUM(CASE TypeID WHEN ' + RTRIM(id)
+ ' THEN Fee END)'
FROM [Type]
EXEC('
SELECT
月份 = CONVERT(char(7), Date, 120)
' + @s + ',
总计 = SUM(Fee)
FROM Fee
GROUP BY CONVERT(char(7), Date, 120)
')