前端之家

  1. 首页
  2. 问答

添加一个新列，该列根据现有字段值自动递增

• 问答

不确定如何执行此操作，但是我想添加一个新列“ Jersey”，该列将根据tab_A和tab_B中的“运动”值递增1。因此，如果tab_B中已经存在“运动”，则只需抓住最大jersey_no并为新列添加1。那很容易。

现在，如果tab_B中不存在“运动”，则将新列的“运动”值设为100。但是，如果tab_A中有多个相同的“运动”（但tab_B中不存在），则它应从100开始并为下一个相同运动增加1，依此类推（例如，参见下面的Garcia示例） ）。

我创建了一个序列“ sequnce”，但是那根本没有帮助。还有另一种方法可以做到这一点吗？预先感谢！

Tab_A 

 Name       State       Sport           
 Garcia      CA         Basketball      
 Garcia      AL         Basketball      
 Garcia      NY         Basketball      
 McGee       CA         Swimming        
 Tontou      CA         Football        
 Tontou      AL         Swimming

Tab_B 

 Name     Sport         Jersey_No
 Garcia   Swimming      100
 Garcia   Football      100
 McGee    Swimming      101
 Tontou   Swimming      101
 Tontou   Swimming      102

预期产量

 Name       State       Sport           Jersey
 Garcia      CA         Basketball      100
 Garcia      AL         Basketball      101
 Garcia      NY         Basketball      102
 McGee       CA         Swimming        102    
 Tontou      CA         Football        100
 Tontou      AL         Swimming        103

我的代码

select name,state,sport,nvl ((select max(b.jersey_no + 1) from tab_b b
                where b.sport = a.sport
                and   b.name = a.name),(case
        when not exists (select 1 from tab_b b 
                where b.sport = a.sport
                and   b.name = a.name
                having count(a.sport) > 1) 
        then seqnce.nextval 
        else '100'
        end )
        ) Jersey
from tab_a
wuyangzhijy 回答：添加一个新列，该列根据现有字段值自动递增

如果仅选择结果，则使用row_number（）。如果需要更新表中的列，则编写触发器

例如：

WITH taba AS
(SELECT 'Garcia'  Name,'CA'  State,'Basketball'  Sport  from dual
UNION ALL
SELECT 'Garcia'  Name,'AL'  State,'NY'  State,'Basketball'  Sport  from dual 
UNION ALL
SELECT 'McGee'  Name,'Swimming'  Sport  from dual 
UNION ALL
SELECT 'Tontou'  Name,'Football'  Sport  from dual
UNION ALL
SELECT 'Tontou'  Name,'Swimming'  Sport  from dual),tabb AS
(SELECT 'Garcia'  Name,'Swimming'  Sport,100 Jersey   from dual
UNION ALL
SELECT 'Garcia'  Name,'Football',100 from dual
UNION ALL
SELECT 'McGee'  Name,'Swimming',101 from dual 
UNION ALL
SELECT 'Tontou'  Name,101 from dual
UNION ALL
SELECT 'Tontou'  Name,102 from dual)
SELECT taba.Name,taba.State,taba.Sport,row_number() over(partition by taba.Name,taba.Sport ORDER BY taba.State) 
            + nvl((SELECT MAX(tabb.Jersey) 
                    FROM tabb
                    WHERE taba.name = tabb.name 
                    AND taba.sport = tabb.sport),99) 
FROM taba

结果：

Garcia  AL  Basketball  100
Garcia  CA  Basketball  101
Garcia  NY  Basketball  102
McGee   CA  Swimming    102
Tontou  CA  Football    100
Tontou  AL  Swimming    103
oracleplsqlsql
本文链接：https://www.f2er.com/3140451.html

大家都在问