sql - 有没有好的方法在 SQL 中做到这一点？

Question

我正在尝试完全使用 SQL(Sybase ASE 12 中的 ANSI 或 TSQL)解决以下问题，而不依赖游标或基于循环的逐行处理。

_{注意:我已经创建了一个在应用程序层实现相同目标的解决方案(因此请不要用“不要在 SQL 中执行此操作”来“回答”)，但作为原则问题(希望如此)提高性能)我想知道是否有一个高效(例如无游标)的纯 SQL 解决方案。}

设置:

• 我有一个表 T，包含以下 3 列(全部不为 NULL):

  ---- Table T -----------------------------
  | item  | tag           | value          | 
  | [int] | [varchar(10)] | [varchar(255)] | 
  

• 表格在项目、标签上有唯一索引

• 每个标签都具有字符串“TAG##”的形式，其中“##”是数字 1-99

• 现有标签不保证是连续的，例如项目 13 可能有标签“TAG1”、“TAG3”、“TAG10”。

• TASK:我需要将一堆新行从另一个表 T_NEW 插入表中，该表只有项目和值，并为它们分配新标签，这样它们就不会违反项目、标签上的唯一索引。

  值的唯一性无关紧要(假设 item+value 始终是唯一的)。

  ---- Table T_NEW --------------------------
  | item  | tag            | value          | 
  | [int] | STARTS AS NULL | [varchar(255)] | 
  

• 问题:如何为表 T_NEW 中的所有行分配新标签，以便:

  • T 和 T_NEW 联合中的所有项目+标签组合都是唯一的

  • 新分配的标签应全部采用“TAG##”形式

  • 新分配的标签最好应该是给定项目可用的最小标签。

• 如果有帮助的话，您可以假设我已经有一个临时表#tags，其中“标签”列包含 99 行，其中包含所有有效标签(TAG1..TAG99 ，每行一个)

Answer 1

我启动了 fiddle这将为您提供按项目可用的“打开”标签的列表。它使用 #tags (AllTags) 并执行 outer-join-where-null 来完成此操作。您可以使用它从 T_New 插入新标签...

with T_openTags as (
  select 
    items.item,
    openTagName = a.tag
  from
    (select distinct item from T) items
    cross join AllTags a
    left outer join T on 
      items.item = T.item
      and T.tag = a.tag
  where
    T.item is null
 )

select * from T_openTags

或查看此updated fiddle对 T_New 表进行更新。本质上是添加一个 row_number ，以便我们可以选择正确的开放标记在单个更新语句中使用。我用前导零填充标签名称以简化排序。

with T_openTags as (
  select 
    items.item,
    openTagName = a.tag,
    rn = row_number() over(partition by items.item order by a.tag)
  from
    (select distinct item from T) items
    cross join AllTags a
    left outer join T on 
      items.item = T.item
      and T.tag = a.tag
  where
    T.item is null

), T_New_numbered as (

  select *, 
     rn = row_number() over(partition by item order by value) 
  from T_New
)

update tnn set tag = openTagName
from T_New_numbered tnn
inner join T_openTags tot on 
  tot.item = tnn.item
  and tot.rn = tnn.rn


select * from T_New

已更新fiddle可怜的人 row_number 替换仅适用于不同的 T_New 值