我有一张桌子，上面有如下记录
NAME STATUS xml_configparamdb_id xml_configuration_id
STO INACTIVE 1 1
STO ACTIVE 1 2
BOS ACTIVE 1 3
KYC INACTIVE 1 4
KYC INACTIVE 1 5
ACC ACTIVE 1 6
ACC ACTIVE 1 7
现在我感兴趣的结果如下：
NAME STATUS xml_configparamdb_id xml_configuration_id
STO ACTIVE 1 2
BOS ACTIVE 1 3
KYC INACTIVE 1 4
ACC ACTIVE 1 6
也就是说，我想根据STATUS选择数据。
条件-如果STATUS对于相同参数的两种情况均处于活动状态-选择第一个进入活动状态
条件-如果两个参数相同的情况下STATUS为INACTIVE-选择第一个进入的INACTIVE
条件-如果相同参数的状态为活动和不活动-选择活动
现在，我使用下面的查询来填充类似上面的结果，而无需使用PRIMARY KEY Column（xml_configuration_id）
CURSOR cCnfgTypData IS
select distinct name, description, STATUS
from stg_xml_cpdb_configuration
WHERE process_exec_num = 1
AND STATUS = 'ACTIVE'
UNION ALL
select name, description, STATUS
from stg_xml_cpdb_configuration t
where process_exec_num = 1
and STATUS = 'INACTIVE'
and not exists (select * from stg_xml_cpdb_configuration
where name = t.name
and STATUS = 'ACTIVE') order by name, description;
它显示出良好的数据。但是当我使用如下所示的PRIMARY KEY Column（xml_configuration_id）执行时，它会显示所有数据，但不满足条件
select distinct name, description, STATUS, xml_configparamdb_id, xml_configuration_id
from stg_xml_cpdb_configuration
WHERE process_exec_num = 1
AND STATUS = 'ACTIVE'
UNION ALL
select name, description, STATUS, xml_configparamdb_id, xml_configuration_id
from stg_xml_cpdb_configuration t
where process_exec_num = 1
and STATUS = 'INACTIVE'
and not exists (select * from stg_xml_cpdb_configuration
where name = t.name
and STATUS = 'ACTIVE') order by name, description;
使用解析功能ROW_NUMBER。
SQL> SELECT name,
2 status,
3 xml_configparamdb_id,
4 xml_configuration_id
5 FROM
6 ( SELECT t.*, row_number() over (partition BY name order by status) rn FROM t
7 )
8 WHERE rn = 1
9 ORDER BY xml_configuration_id
10 /
NAM STATUS XML_CONFIGPARAMDB_ID XML_CONFIGURATION_ID
--- -------- -------------------- --------------------
STO ACTIVE 1 2
BOS ACTIVE 1 3
KYC INACTIVE 1 4
ACC ACTIVE 1 6
SQL>
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句