我的查詢：
SELECT ami.unit_type,ami.unit_nr, nwmp.metering_point_id,CR.register_name from nw_metering_connection@AMIAMI AMI
LEFT JOIN NW_METERING_POINT@amiami nwmp ON (nwmp.internal_metering_point_id=ami.internal_metering_point_id)
LEFT JOIN NW_UNIT_CONFIG NUC ON (NUC.UNIT_NR=AMI.UNIT_NR)
LEFT JOIN CFG_CONFIGURATION CFG ON ( nuc.configuration_id=CFG.CONFIGURATION_ID)
LEFT JOIN CFG_REGISTER CR ON (CR.CONFIGURATION_ID=NUC.CONFIGURATION_ID AND CR.CONFIGURATION_ID=CFG.CONFIGURATION_ID)
WHERE ami.unit_type=58 and ami.input_nr in (1,2) and ami.valid_until is null and nuc.valid_until is null and CR.REGISTER_TYPE=8 and CR.register_nr in(5,6) ;
我需要找到unit_nrnot havingregister_nr=6因為它只有register_nr=5而不是兩者都有。如果兩者都有，我就不需要找到它。
uj5u.com熱心網友回復：
您可以將COUNT分析函式與CASE運算式一起使用：
SELECT unit_type,
unit_nr,
metering_point_id,
register_name
FROM (
SELECT ami.unit_type,
ami.unit_nr,
nwmp.metering_point_id,
CR.register_name,
COUNT(CASE cr.register_nr WHEN 5 THEN 1 END) OVER (PARTITION BY ami.unit_nr) AS num5,
COUNT(CASE cr.register_nr WHEN 6 THEN 1 END) OVER (PARTITION BY ami.unit_nr) AS num6
from nw_metering_connection@AMIAMI AMI
LEFT JOIN NW_METERING_POINT@amiami nwmp ON (nwmp.internal_metering_point_id=ami.internal_metering_point_id)
LEFT JOIN NW_UNIT_CONFIG NUC ON (NUC.UNIT_NR=AMI.UNIT_NR)
LEFT JOIN CFG_CONFIGURATION CFG ON ( nuc.configuration_id=CFG.CONFIGURATION_ID)
LEFT JOIN CFG_REGISTER CR ON (CR.CONFIGURATION_ID=NUC.CONFIGURATION_ID AND CR.CONFIGURATION_ID=CFG.CONFIGURATION_ID)
WHERE ami.unit_type=58
AND ami.input_nr in (1,2)
AND ami.valid_until is null
AND nuc.valid_until is null
AND CR.REGISTER_TYPE=8
AND CR.register_nr in(5,6)
)
WHERE num5 > 0
AND num6 = 0;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/533985.html
標籤：数据库甲骨文12c
上一篇：帶有比較的SQLwhere子句
下一篇：每小時計數MySQL