34,593
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[monthlj]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[monthlj]
GO
CREATE TABLE [dbo].[monthlj] (
[采集日期] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[甲线煤量累计] [real] NULL ,
[甲线石灰石累计] [real] NULL ,
[甲线耗电量累计] [real] NULL ,
[乙线煤量累计] [real] NULL ,
[乙线石灰石累计] [real] NULL ,
[乙线耗电量累计] [real] NULL ,
[负荷1累计] [real] NULL ,
[负荷2累计] [real] NULL ,
[负荷3累计] [real] NULL
) ON [PRIMARY]
GO
select
sum(case when datepart(mm,采集日期)=1 then 甲线煤量累计 else 0 end) as '甲线煤量累计_01月',
sum(case when datepart(mm,采集日期)=2 then 甲线煤量累计 else 0 end) as '甲线煤量累计_02月',
...
from
monthlj
where
datepart(yy,采集日期) = 2011
select
left([采集日期],6) as 年月,
SUM([甲线煤量累计]) AS [甲线煤量累计],
............
from dbo].[monthlj]
group by left([采集日期],6)
CREATE PROCEDURE p_monthlj_Year(
@dt int
)
SELECT MONTH([采集日期]) AS 月份,
SUM([甲线煤量累计]) AS [甲线煤量累计],
............
FROM dbo].[monthlj]
WHERE YEAR([采集日期])=@dt
GROUP BY MONTH([采集日期])
go
--調用
EXEC p_monthlj_Year 2011
Select Convert(Varchar(7),采集日期,120) As 月份,
Sum(isNull(甲线煤量累计,0)) As 甲线煤量累计,
Sum(isNull(甲线石灰石累计,0)) As 甲线石灰石累计,
Sum(isNull(甲线耗电量累计,0)) As 甲线耗电量累计,
Sum(isNull(乙线煤量累计,0)) As 乙线煤量累计,
Sum(isNull(乙线石灰石累计,0)) As 乙线石灰石累计,
Sum(isNull(乙线耗电量累计,0)) As 乙线耗电量累计,
Sum(isNull(负荷1累计,0)) As 负荷1累计,
Sum(isNull(负荷2累计,0)) As 负荷2累计,
Sum(isNull(负荷3累计,0)) As 负荷3累计
From monthlj
Where Convert(Varchar(4),采集日期,120) = '2011'
Group By Convert(Varchar(7),采集日期,120)
select
sum(case datepart(mm,采集日期) when 1 then 甲线煤量累计 else 0 end) [甲线煤量累计_01月],
sum(case datepart(mm,采集日期) when 2 then 甲线煤量累计 else 0 end) [甲线煤量累计_02月],
...
sum(case datepart(mm,采集日期) when 12 then 甲线煤量累计 else 0 end) [甲线煤量累计_12月],
如果还需要合计其他字段则如上写法即可。
from monthlj
where datepart(yy,采集日期) = 2011
select
left([采集日期],6) as 年月,
SUM(isnull([甲线煤量累计],0)) AS [甲线煤量累计],
SUM(isnull([甲线石灰石累计],0)) AS [甲线煤量累计],
...
SUM(isnull([负荷2累计],0)) AS [负荷2累计],
SUM(isnull([负荷3累计],0)) AS [负荷3累计]
from dbo].[monthlj]
group by left([采集日期],6)
order by left([采集日期],6)