SELECT
BRONGTH_NAME,
BRONGTH_SN,
CMHD.DATA_CHANNEL_SN,
TRUNC(CMHD.GETDATE,'MM') GET_MONTH,
MIN(GETDATE) MIN_MONTH,
MAX(GETDATE) MAX_MONTH FROM CD_MONI_H_DATA CMHD
JOIN V_STATION_CHANNEL VSC
ON CMHD.DATA_CHANNEL_SN = VSC.DATA_CHANNEL_SN
JOIN V_PUMP_STATION VPS
ON VSC.MCSTA_SN = VPS.MCSTA_SN
JOIN V_NODE_AREA VNA
ON VPS.NODE_SN = VNA.NODE_SN WHERE CMHD.GETDATE >= TO_DATE(' 2012-01-01','YYYY-MM-DD')
AND CMHD.GETDATE <= (ADD_MONTHS(TO_DATE(' 2012-01-01','YYYY-MM-DD'),12))
GROUP BY BRONGTH_NAME, BRONGTH_SN, CMHD.DATA_CHANNEL_SN, TRUNC(CMHD.GETDATE,'MM')
最后一行GROUP BY .......使速度很慢。有没有更有效率的写法?
BRONGTH_NAME,
BRONGTH_SN,
CMHD.DATA_CHANNEL_SN,
TRUNC(CMHD.GETDATE,'MM') GET_MONTH,
MIN(GETDATE) MIN_MONTH,
MAX(GETDATE) MAX_MONTH FROM CD_MONI_H_DATA CMHD
JOIN V_STATION_CHANNEL VSC
ON CMHD.DATA_CHANNEL_SN = VSC.DATA_CHANNEL_SN
JOIN V_PUMP_STATION VPS
ON VSC.MCSTA_SN = VPS.MCSTA_SN
JOIN V_NODE_AREA VNA
ON VPS.NODE_SN = VNA.NODE_SN WHERE CMHD.GETDATE >= TO_DATE(' 2012-01-01','YYYY-MM-DD')
AND CMHD.GETDATE <= (ADD_MONTHS(TO_DATE(' 2012-01-01','YYYY-MM-DD'),12))
GROUP BY BRONGTH_NAME, BRONGTH_SN, CMHD.DATA_CHANNEL_SN, TRUNC(CMHD.GETDATE,'MM')
最后一行GROUP BY .......使速度很慢。有没有更有效率的写法?
解决方案 »
- 学习Oracle EBS 开发相关
- 请教:按用户数购买Oracle在实际应用中如果超出该用户数是何结果
- 如何实现横向排列数据
- 探讨一下oracle中有2千万条数据,如果进行模糊查询呢???
- SELECT A.SiHai_CODE, @@@@@ form table这里的@@@@@ 是什么
- 我在RedhatLinux 9.2上装oracle 10g 遇到的问题,请各位高手帮帮忙!!!(来者有益)
- 远程访问oracle9i数据库,如何操作?
- Oracle 11g 64位 备份 恢复
- 如何用vb控制oracle中的触发器、视图、存储过程?
- 谁有带注册码的rapidsql可以下载
- 数据库sql语句求教
- 关于Oracle连接报错的问题!!!
你就这一句SQL大家不知道发生了什么
select
BRONGTH_NAME,
BRONGTH_SN,
DATA_CHANNEL_SN,
GET_MONTH,
MIN(GETDATE) MIN_MONTH,
MAX(GETDATE) MAX_MONTH
from
(
SELECT
BRONGTH_NAME,
BRONGTH_SN,
CMHD.DATA_CHANNEL_SN,
TRUNC(CMHD.GETDATE,'MM') GET_MONTH,
GETDATE
FROM CD_MONI_H_DATA CMHD
JOIN V_STATION_CHANNEL VSC
ON CMHD.DATA_CHANNEL_SN = VSC.DATA_CHANNEL_SN
JOIN V_PUMP_STATION VPS
ON VSC.MCSTA_SN = VPS.MCSTA_SN
JOIN V_NODE_AREA VNA
ON VPS.NODE_SN = VNA.NODE_SN WHERE CMHD.GETDATE >= TO_DATE(' 2012-01-01','YYYY-MM-DD')
AND CMHD.GETDATE <= (ADD_MONTHS(TO_DATE(' 2012-01-01','YYYY-MM-DD'),12))
)
GROUP BY BRONGTH_NAME, BRONGTH_SN, DATA_CHANNEL_SN, GET_MONTH