代码之家 › 专栏 › 技术社区 › Logan Wlv

Oracle SQL标记每个第一个副本都带有true,其他则带有false

oracle sql

-1

Logan Wlv · 技术社区 · 6 年前

作为SQL Oracle的初学者,我正在开发一个表 CATS 有4个varchar2字段 country , hair , color 和 firstItemFound . 我试图编辑一个SQL请求,所以每次我点击一个新的元组 name || country || color ,我添加了一个属性 'true' 如果我已经找到这个元组,我就添加一个属性 'false' .

我想到了这个:

步骤1) 更新(从cats中选择不同的名称、国家、颜色)tmp_cats set firstitemfound=true;

步骤2) update cats set 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

2 回复 | 直到 6 年前

Zaynul Abadin Tuhin 6 年前

你可以使用 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

Barbaros Özhan 6 年前

您可以使用“包含”和“额外”列(如 id 订购:

with t(id,country,hair,colour) as
(
 select 1,'France', 'Shorthair', 'Red' from dual union all
 select 2,'Brazil', 'Longhair', 'Yellow' from dual union all
 select 3,'France', 'Shorthair', 'Red' from dual
)
 select country,hair,colour, 
        (case when ( dense_rank() over (partition by country,hair,colour order by id) = 1 ) 
             then 'true'
             else 'false'
        end) as firstItemFound          
   from t 
  order by id;

COUNTRY HAIR       COLOUR   FIRSTITEMFOUND
France  Shorthair   Red         true
Brazil  Longhair    Yellow      true
France  Shorthair   Red         false