4,011
社区成员
发帖
与我相关
我的任务
分享orcal 语句求大神帮忙优化一下
SELECT E.ITEM_ID,E.ACQUISITIONTIME,E.TIMESTAMP FROM
(SELECT D.DEVICE_ID,D.DEV_SN,B.V_CODE FROM YUN_BASE_DEVICE_DETAILS D,YUN_BASE_COLLECTVARIATE B
WHERE 1=1 AND D.DEVICE_TYPE_ID!=1 AND D.LAST_ONLINE_TIME IS NOT NULL AND D.DEVICE_ID = B.COLLECT_ID)S
INNER JOIN
EMS_DATAACQUISITION E on E.ITEM_ID = CONCAT(S.DEV_SN,S.V_CODE)
INNER JOIN
(SELECT ITEM_ID,MAX(TIMESTAMP) AS TIMESTAMP
FROM EMS_DATAACQUISITION GROUP BY ITEM_ID)ED
ON ED.ITEM_ID = E.ITEM_ID AND ED.TIMESTAMP = E.TIMESTAMP
ORDER BY E.TIMESTAMP DESC
跑完要19S 数据太多了~求大神优化！感激不尽！！
(SELECT D.DEVICE_ID,D.DEV_SN,B.V_CODE FROM YUN_BASE_DEVICE_DETAILS D,YUN_BASE_COLLECTVARIATE B
WHERE 1=1 AND D.DEVICE_TYPE_ID!=1 AND D.LAST_ONLINE_TIME IS NOT NULL AND D.DEVICE_ID = B.COLLECT_ID)S
INNER JOIN
EMS_DATAACQUISITION E on E.ITEM_ID = CONCAT(S.DEV_SN,S.V_CODE)
INNER JOIN
(SELECT ITEM_ID,MAX(TIMESTAMP) AS TIMESTAMP
FROM EMS_DATAACQUISITION GROUP BY ITEM_ID)ED
ON ED.ITEM_ID = E.ITEM_ID AND ED.TIMESTAMP = E.TIMESTAMP
ORDER BY E.TIMESTAMP DESC
跑完要19S 数据太多了~求大神优化！感激不尽！！
...全文
请发表友善的回复…
发表回复
cfghfgskrte 2017-07-26
- 打赏
- 举报
用with试下：
with CONCATTABLE(DEVICE_ID, DEV_SN, CONCATID)
as
(
select D.DEVICE_ID ,
D.DEV_SN ,
CONCAT(S.DEV_SN,S.V_CODE) as CONCATID
from YUN_BASE_DEVICE_DETAILS D ,
YUN_BASE_COLLECTVARIATE B
where 1 = 1
and D.DEVICE_TYPE_ID != 1
and D.LAST_ONLINE_TIME is not null
and D.DEVICE_ID = B.COLLECT_ID
)
select E.ITEM_ID ,
E.ACQUISITIONTIME ,
E.TIMESTAMP
from EMS_DATAACQUISITION E
inner join CONCATTABLE S on(E.ITEM_ID = S.CONCATID)
inner join ( select ITEM_ID ,
max(TIMESTAMP) as TIMESTAMP
from EMS_DATAACQUISITION
group by ITEM_ID
) ED on (ED.ITEM_ID = E.ITEM_ID
and ED.TIMESTAMP = E.TIMESTAMP)
order by E.TIMESTAMP desc
sevancheng 2017-06-06
- 打赏
- 举报
(SELECT ITEM_ID,MAX(TIMESTAMP) AS TIMESTAMP
FROM EMS_DATAACQUISITION GROUP BY ITEM_ID)ED 这个往前面放试试
