oracle 横列减法,oracle sql小结（主要讲横列转换的例子）group by以及wmsys.wm_concat()的使用...

---计算九月每个电厂的数量

select f_dcname，count(f_dcname) as 九月份的数量 from W_EC_PLACESTATION_COLLECT t

where f_collectdate >= TRUNC(TO_DATE('2018-09-01','yyyy-MM-dd'),'month') AND f_collectdate < TO_DATE('2018-09-30','yyyy-MM-dd')+1

group by f_dcname

----计算九月份每个电厂对应的每个日期(左链接)

select a.f_dcname，a.f_collectdate from W_EC_PLACESTATION_COLLECT a

left join W_EC_PLACESTATION_COLLECT b on a.f_dcname = b.f_dcname and a.f_collectdate= b.f_collectdate

where a.f_collectdate >= TRUNC(TO_DATE('2018-09-01','yyyy-MM-dd'),'month') AND a.f_collectdate < TO_DATE('2018-09-30','yyyy-MM-dd')+1

and a.f_dcname like '%光伏%'

order by a.f_dcname,a.f_collectdate

----计算九月份每个日期对应的每个电厂(group by两个字段)

select f_collectdate ,f_dcname from W_EC_PLACESTATION_COLLECT t

where f_collectdate >= TRUNC(TO_DATE('2018-09-01','yyyy-MM-dd'),'month') AND f_collectdate < TO_DATE('2018-09-30','yyyy-MM-dd')+1

and f_dcname like '%光伏%'

group by f_collectdate ,f_dcname order by f_collectdate

--合并电厂的日期(行列转换)

select f_dcname ,wmsys.wm_concat(to_char(f_collectdate,'dd')) from W_EC_PLACESTATION_COLLECT t

where f_collectdate >= TRUNC(TO_DATE('2018-09-01','yyyy-MM-dd'),'month') AND f_collectdate < TO_DATE('2018-09-30','yyyy-MM-dd')+1

group by f_dcname

--合并电厂的日期(行列转换)并让合并的日期进行排序

select f_dcname ,max(r)

from (

select f_dcname , wmsys.wm_concat(to_char(f_collectdate,'dd'))

over(partition by f_dcname order by f_collectdate ) r

from W_EC_PLACESTATION_COLLECT t

where f_collectdate >= TRUNC(TO_DATE('2018-09-01','yyyy-MM-dd'),'month') AND f_collectdate < TO_DATE('2018-09-30','yyyy-MM-dd')+1

)

group by f_dcname