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 .......使速度很慢。有没有更有效率的写法?
你就这一句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