时间戳

首页 > 解决方案 > Oracle sql将每个第一个重复标记为true，其他标记为false

sql - Oracle sql将每个第一个重复标记为true，其他标记为false

问题描述

作为 SQL oracle 的初学者，我正在处理一个CATS包含 4 个 varchar2 字段country、hair和. 我正在尝试编辑一个 sql 请求，所以每次我点击一个新的元组时，如果我已经找到这个元组，我会添加一个属性，我会添加一个属性。colorfirstItemFoundname || country || color'true''false'

我想过这个：

步骤 1)更新（从猫中选择不同的名称、国家、颜色） tmp_cats set firstItemFound = true;

步骤 2)更新猫设置 firstItemFound = false 其中 firstItemFound 为空；

但是1)不起作用，因为您无法更新非物理视图。有什么解决办法吗？是否可以一次操作而不是两次操作？

这是我的表值（firstItemFound 列有空值）：

NAME   |COUNTRY   |COLOR  |
-------|----------|-------|
France |Shorthair |Red    |
Brazil |Longhair  |Yellow |
France |Shorthair |Red    |
France |Longhair  |Brown  |
France |Longhair  |Black  |
Brazil |Longhair  |Yellow |
Brazil |Longhair  |Black  |
Brazil |Longhair  |Brown  |
Brazil |Longhair  |Yellow |

这是我想要的结果：

country    hair       color    firstItemFound
---------------------------------------------
France   Shorthair     Red      true
France   Shorthair     Red      false
France   Longhair     Brown     true
France   Longhair     Black     true
Brazil   Longhair     Yellow    true
Brazil   Longhair     Yellow    false
Brazil   Longhair     Yellow    false
Brazil   Longhair     Black     true
Brazil   Longhair     Brown     true

标签： sqloracle

解决方案

你可以row_number用来使第一项为真

select *,case when rn=1 then true else false end as firstItemFound  from 
(select country,hair,colur, row_number() 
over(partition by country,hair,colur order by country) rn from t
) t

或者你可以使用 cte 表达式

with t as (
select * from 
(
select 'France' as country  ,'Shorthair' as hair,'Red' as colur from dual
union all 
select 'Brazil','Longhair','Yellow' from dual
union all
select 'France' ,'Shorthair','Red' from dual

)  
) , t2 as
(
select country,hair,colur, row_number() 
over(partition by country,hair,colur order by country) rn from t

)select t2.*, case when rn=1 then 'true' else 'false' end as firstItemFound  from t2

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=65146824ed31dbbc6daa40676220cdf8

COUNTRY     HAIR    COLUR   RN  FIRSTITEMFOUND
Brazil  Longhair    Yellow  1   true
France  Shorthair   Red     1   true
France  Shorthair   Red     2   false

推荐阅读