我的数据库中有两个表，如下表所示。在每个表DETEM_MENT_CODE的第一个ITEM表中，将有多个ITEM_CODE。
ITEM ---------------------------------------------------- "STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "011" "912003" "14" "011" "912004" "14" "011" "914001" "14" ---------------------------------------------------- COMPETITOR -------------------------------------------------------------- "STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "COMPETITOR_CODE" "011" "912003" "14" "01" "011" "912003" "14" "02" "011" "912003" "14" "03" "011" "912004" "14" "01" "011" "912004" "14" "02" "011" "912004" "14" "04" "011" "914001" "14" "01" "011" "914001" "14" "02" "011" "914001" "14" "03" -------------------------------------------------------------
在表COMPETITOR evey ITEMCODE中将有三个条目，并且具有不同的competitor_Code
我有三个值Comp_1，comp_2，comp_3和department_code = 14;
我想做的是用comp_1，comp_2，comp_3更新COMPETITOR表，以获取每个项目的ITEM表中的Item_code（项目代码为14）
样本输出
COMPETITOR -------------------------------------------------------------- "STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "COMPETITOR_CODE" "011" "912003" "14" "Comp_1" "011" "912003" "14" "Comp_2" "011" "912003" "14" "Comp_3" "011" "912004" "14" "Comp_1" "011" "912004" "14" "Comp_2" "011" "912004" "14" "Comp_3" "011" "914001" "14" "Comp_1" "011" "914001" "14" "Comp_2" "011" "914001" "14" "Comp_3" -------------------------------------------------------------
我该如何为此编写单个oracle查询？
以下内容假定ITEM_CODE仅分配给一个DEPARTMENT_CODE，因为它很简单并且您没有给我们进一步的业务规则。如果此假设是错误的，则需要相应地调整逻辑。
我还结合了您的要求，即COMPETITOT.COMPETITOR_CODE的现有值不可靠。
给定这个测试日期：
SQL> select * from competitor 2 / STORE_CODE ITEM_CODE DEPARTMENT_CODE COMPETITOR ---------- ---------- --------------- ---------- 11 912003 14 01 11 912003 14 04 11 912003 14 03 11 912004 14 01 11 912004 14 02 11 912004 14 04 11 914001 14 01 11 914001 14 02 11 914001 14 05 9 rows selected. SQL>
我们可以使用解析ROW_NUMBER（）为COMPETITOR_CODE生成必要的句柄：
SQL> update competitor c 2 set competitor_code = 3 (select decode (dr 4 , 1, 'Comp_1' 5 , 2, 'Comp_2' 6 , 3, 'Comp_3') 7 from ( select row_number() over ( partition by x.item_code 8 order by x.rowid ) as dr 9 , x.rowid as row_id 10 from competitor x 11 where x.item_code in ( select item_code 12 from item 13 where department_code = 14 ) ) l 14 where c.rowid = l.rowid ) 15 / 9 rows updated. SQL>
这是期望的结果（除非对业务规则进行任何其他添加）：
SQL> select * from competitor 2 / STORE_CODE ITEM_CODE DEPARTMENT_CODE COMPETITOR ---------- ---------- --------------- ---------- 11 912003 14 Comp_1 11 912003 14 Comp_2 11 912003 14 Comp_3 11 912004 14 Comp_1 11 912004 14 Comp_2 11 912004 14 Comp_3 11 914001 14 Comp_1 11 914001 14 Comp_2 11 914001 14 Comp_3 9 rows selected. SQL>
