sql语句如何选择年 查询出1到12月的数据 已经有了按月查询每日的语句

[sinat_40026833]

下面是每日的查询方法 应该是在这个基础上改一下就OK 改了一天了 改不出来 数据一直乱

SELECT *FROM 
(SELECT 計量種類,date,c FROM 
           (SELECT 
DATEPART(DAY,convert(VARCHAR(10),U.GR_DATE,112))as date,
ISNULL(CONVERT(Varchar(10),K.total ),0)AS '每日用電',
ISNULL(convert(VARCHAR(10),(CAST(QTY AS INT)/1000)),0) AS '每日UTS',
ISNULL(CONVERT(Varchar(10),ROUND(( CAST(QTY AS FLOAT) / CAST(total AS FLOAT)),2)),0) AS '能耗改善率',
ISNULL(CONVERT(Varchar(10),T3.rmb ),0) AS '每日用電費用'
FROM KWH K,
UTS U,
(select CONVERT(CHAR(10),dt,120) AS dt,sum(cast(t.total as int)*c.feerate) as rmb
from KWH as t
inner join (values(0,8,0.2909),(8,12,1.0387),(12,17,0.6148),(17,21,1.0387),(22,24,0.6148)) c (FromHour,ToHour,feerate) 
on datepart(hour,t.dt)>=c.FromHour and datepart(hour,t.dt)<c.ToHour
where DATEDIFF(MONTH,t.dt,'2017-08-02')=0 and type = 'R'
GROUP BY CONVERT(CHAR(10),dt,120))T3
WHERE 
K.did = '190164'
AND K.[type] = 'D'
AND U.PRODUCTLINECODE = '23'
AND CONVERT(CHAR(10), K.dt, 121) = CONVERT(CHAR(10), U.GR_DATE, 121)
AND CONVERT(CHAR(10), K.dt, 121) = CONVERT(CHAR(10),T3.dt, 121)
AND DATEDIFF(MONTH,GR_DATE,'2017-08-02')=0
) p 
           UNPIVOT(c FOR 計量種類 IN (每日用電,每日UTS,能耗改善率,每日用電費用))  AS unpvt) T 
            PIVOT (MAX(T.c) FOR T.date in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
           [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],
           [22],[23],[24],[25],[26],[27],[28],[29],[30],[31]))PT
ORDER BY
case [計量種類]
  when '每日用電' then 1
  when '每日UTS' then 2
  when '能耗改善率' then 3 
  when '每日用電費用' then 4
END

这是查询的结果 后面的没有截出来 有31列的数据就是31天


这是我想要实现的结果

Q1 Q2 Q3 Q4就是每个季度3个月相加的值

求助 各位老师

[听雨停了]

SELECT datepart(year,CONVERT(VARCHAR(10), U.GR_DATE, 112)) AS [Year], datepart(month,CONVERT(VARCHAR(10), U.GR_DATE, 112)) AS [MONTH], sum(convert(NUMERIC(8,2),ISNULL(CONVERT(VARCHAR(10), K.total), 0))) AS '每月用電', sum(convert(NUMERIC(8,2),ISNULL(CONVERT(VARCHAR(10), (CAST(QTY AS INT) / 1000)), 0))) AS '每月UTS', sum(convert(NUMERIC(8,2),ISNULL( ROUND((CAST(QTY AS FLOAT) / CAST(total AS FLOAT)), 2), 0 )))/COUNT(t3.dt) AS '能耗改善率', sum(convert(NUMERIC(8,2),ISNULL(CONVERT(VARCHAR(10), T3.rmb), 0))) AS '每月用電費用' FROM KWH K, UTS U, ( SELECT CONVERT(CHAR(10), dt, 120) AS dt, SUM(CAST(t.total AS INT) * c.feerate) AS rmb FROM KWH AS t INNER JOIN ( VALUES(0, 8, 0.2909), (8, 12, 1.0387), (12, 17, 0.6148), (17, 21, 1.0387), (22, 24, 0.6148) ) c(FromHour, ToHour, feerate) ON DATEPART(hour, t.dt) >= c.FromHour AND DATEPART(hour, t.dt) < c.ToHour WHERE TYPE = 'R' GROUP BY CONVERT(CHAR(10), dt, 120) )T3 WHERE K.did = '190164' AND K.[type] = 'D' AND U.PRODUCTLINECODE = '23' AND CONVERT(CHAR(10), K.dt, 121) = CONVERT(CHAR(10), U.GR_DATE, 121) AND CONVERT(CHAR(10), K.dt, 121) = CONVERT(CHAR(10), T3.dt, 121) GROUP BY datepart(year,CONVERT(VARCHAR(10), U.GR_DATE, 112)),datepart(month,CONVERT(VARCHAR(10), U.GR_DATE, 112))

你把以上代码在你那边跑一下，如果可以抛出数据来，我相信你也应该知道接下来该怎么弄了

[道素]

你第一个结果一行是一个月 数据，你想要的数据一行是一年的数据 你要重新写，至于季度值，你在select 中合计也可以，如Q1=1+2+3

[听雨停了]

建议你先贴出一些元表的数据和结构出来。你这样就一个sql看起来太费劲，完全没有参照

[sinat_40026833]

KWH的表

UTS的表


费用查询的数据