可以用month(Tj_Date)获取月份,然后用pivot()做行列转换..
解决方案 »
- 如何在插入语句的时候给数据库上锁,解锁
- SQLServer还原数据库出错
- insert语句通不过,不知那里错了
- 对于执行一个比较复杂的数据库操作(多步验证、操作等)是使用一个存储过程实现还是在程序中分多个步骤执行多步对数据库操作好呢?
- Mysql
- 请问如何在存储过程里调用其他数据库里面的表的内容?多谢
- SQL SERVER 2000的问题。 大家救我啊。。
- SQL检索的记录中插入Group合计的项目怎么做?
- 怎样客户机上进行sql server 数据库的备份(实现代码)
- 问一个MSDE连接的问题
- 怎么设计数据库去存储某条信息的可能有多个值的属性
- 现在有一个别人已经开发好的数据库,从哪些方面可以快速直接的了解这个数据库
Depart_Code
, Manage_Person
, Tj_Year
, SUM(Tj_Money_Jan) AS Tj_Money_Jan
, SUM(Tj_Money_Feb) AS Tj_Money_Feb
, SUM(Tj_Money_Mar) AS Tj_Money_Mar
, SUM(Tj_Money_Apr) AS Tj_Money_Apr
, SUM(Tj_Money_May) AS Tj_Money_May
, SUM(Tj_Money_Jun) AS Tj_Money_Jun
, SUM(Tj_Money_Jul) AS Tj_Money_Jul
, SUM(Tj_Money_Aug) AS Tj_Money_Aug
, SUM(Tj_Money_Sep) AS Tj_Money_Sep
, SUM(Tj_Money_Oct) AS Tj_Money_Oct
, SUM(Tj_Money_Nov) AS Tj_Money_Nov
, SUM(Tj_Money_Dec) AS Tj_Money_Dec
, SUM(Tj_Money_All) AS Tj_Money_Total
FROM
(
SELECT
Depart_Code
, Manage_Person
, Tj_Year
, CASE Tj_Month WHEN 1 THEN Tj_Money ELSE 0 END AS Tj_Money_Jan
, CASE Tj_Month WHEN 2 THEN Tj_Money ELSE 0 END AS Tj_Money_Feb
, CASE Tj_Month WHEN 3 THEN Tj_Money ELSE 0 END AS Tj_Money_Mar
, CASE Tj_Month WHEN 4 THEN Tj_Money ELSE 0 END AS Tj_Money_Apr
, CASE Tj_Month WHEN 5 THEN Tj_Money ELSE 0 END AS Tj_Money_May
, CASE Tj_Month WHEN 6 THEN Tj_Money ELSE 0 END AS Tj_Money_Jun
, CASE Tj_Month WHEN 7 THEN Tj_Money ELSE 0 END AS Tj_Money_Jul
, CASE Tj_Month WHEN 8 THEN Tj_Money ELSE 0 END AS Tj_Money_Aug
, CASE Tj_Month WHEN 9 THEN Tj_Money ELSE 0 END AS Tj_Money_Sep
, CASE Tj_Month WHEN 10 THEN Tj_Money ELSE 0 END AS Tj_Money_Oct
, CASE Tj_Month WHEN 11 THEN Tj_Money ELSE 0 END AS Tj_Money_Nov
, CASE Tj_Month WHEN 12 THEN Tj_Money ELSE 0 END AS Tj_Money_Dec
, Tj_Money AS Tj_Money_All
FROM (
SELECT
Depart_Code
, Manage_Person
, DATEPART(YYYY, Tj_Date) AS Tj_Year
, DATEPART(MM, Tj_Date) AS Tj_Month
, SUM(Tj_Money) AS Tj_Money
FROM t1
GROUP BY Depart_Code, Manage_Person, DATEPART(YYYY, Tj_Date), DATEPART(MM, Tj_Date)
) a
) b
GROUP BY Depart_Code, Manage_Person, Tj_Year
Depart_Code
, Manage_Person
, Tj_Year
, SUM(CASE Tj_Month WHEN 1 THEN Tj_Money ELSE 0 END) AS Tj_Money_Jan
, SUM(CASE Tj_Month WHEN 2 THEN Tj_Money ELSE 0 END) AS Tj_Money_Feb
, SUM(CASE Tj_Month WHEN 3 THEN Tj_Money ELSE 0 END) AS Tj_Money_Mar
, SUM(CASE Tj_Month WHEN 4 THEN Tj_Money ELSE 0 END) AS Tj_Money_Apr
, SUM(CASE Tj_Month WHEN 5 THEN Tj_Money ELSE 0 END) AS Tj_Money_May
, SUM(CASE Tj_Month WHEN 6 THEN Tj_Money ELSE 0 END) AS Tj_Money_Jun
, SUM(CASE Tj_Month WHEN 7 THEN Tj_Money ELSE 0 END) AS Tj_Money_Jul
, SUM(CASE Tj_Month WHEN 8 THEN Tj_Money ELSE 0 END) AS Tj_Money_Aug
, SUM(CASE Tj_Month WHEN 9 THEN Tj_Money ELSE 0 END) AS Tj_Money_Sep
, SUM(CASE Tj_Month WHEN 10 THEN Tj_Money ELSE 0 END) AS Tj_Money_Oct
, SUM(CASE Tj_Month WHEN 11 THEN Tj_Money ELSE 0 END) AS Tj_Money_Nov
, SUM(CASE Tj_Month WHEN 12 THEN Tj_Money ELSE 0 END) AS Tj_Money_Dec
, SUM(Tj_Money) AS Tj_Money_All
FROM (
SELECT
Depart_Code
, Manage_Person
, DATEPART(YYYY, Tj_Date) AS Tj_Year
, DATEPART(MM, Tj_Date) AS Tj_Month
, SUM(Tj_Money) AS Tj_Money
FROM t1
GROUP BY Depart_Code, Manage_Person, DATEPART(YYYY, Tj_Date), DATEPART(MM, Tj_Date)
) a
GROUP BY Depart_Code, Manage_Person, Tj_Year