讨论：分组查询的问题

Question

select 
        sum(decode(to_char(R.DT,'HH24:MI'),'00:15',PP_0)),
        sum(decode(to_char(R.DT,'HH24:MI'),'00:30',PP_0)),
        sum(decode(to_char(R.DT,'HH24:MI'),'00:45',PP_0)),
        ....
        group by asser_no||trunc(dt,'dd'),
还有张表P，有的字段ID,和asser_no，我还想把trunc(dt,'dd')和P.ID查出来
sql应该怎么写啊？

Answer 1

没有from 之后的表，不知道表之间如何关联，各种不详细啊

Answer 2

SELECT 
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22 :45',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23 :00',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23 :15',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23 :30',R.PC)) ,
SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23 :45',R.PC)) FROM  RD_PVC_POWER_FACTOR@WEISHENG R
WHERE 
EXISTS(
SELECT 1 FROM D_METER D,P_MR_MPED P
WHERE  P.METER_ID = D.METER_ID
AND     D.ASSRET_NO = R.ASSRET_NO
AND    R.ASSRET_NO = E.ASSRET_NO )
GROUP BY R.ASSRET_NO||TRUNC(R.DT,'DD');

Answer 3

你是想把这个结果集和　trunc(dt,'dd')和P.ID　联合起来么　．．．

Answer 4

select R.ASSRET_NO||TRUNC(R.DT,'DD'),sum......

Answer 5

 SELECT R.ASSRET_NO,TRUNC(R.DT,'DD'),
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'00    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'01    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'02    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'03    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'04    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'05    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'06    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'07    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'08    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'09    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'10    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'11    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'12    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'13    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'14    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'15    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'16    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'17    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'18    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'19    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'20    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'21    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'22    :45',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23    :00',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23    :15',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23    :30',R.PC))    ,
        SUM(DECODE(TO_CHAR(R.DT,'HH24:MI'),'23    :45',R.PC))            FROM  RD_PVC_POWER_FACTOR@WEISHENG R
    WHERE 
        EXISTS(
            SELECT 1 FROM D_METER D,P_MR_MPED P
            WHERE  P.METER_ID = D.METER_ID
            AND     D.ASSRET_NO = R.ASSRET_NO
            AND    R.ASSRET_NO = E.ASSRET_NO        )
    GROUP BY R.ASSRET_NO,TRUNC(R.DT,'DD');