将迭代查询更改为基于关系集的查询

2021-09-10 00:00:00 sql tsql sql-server

SQL 小提琴

我试图将迭代/游标查询(工作正常)更改为关系集查询以实现更好的性能，但没有成功.

I'm trying without success to change an iterative/cursor query (that is working fine) to a relational set query to achieve a better performance.

我有什么:

table1

| ID | NAME |
|----|------|
|  1 |    A |
|  2 |    B |
|  3 |    C |


使用函数，我想将我的数据插入到另一个表中.以下函数是一个简化示例:


Using a function, I want to insert my data into another table. The following function is a simplified example:

功能

CREATE FUNCTION fn_myExampleFunction
(
  @input nvarchar(50)
)

RETURNS @ret_table TABLE 
(
  output nvarchar(50)
)

AS

BEGIN
  IF @input = 'A'
    INSERT INTO @ret_table VALUES ('Alice')
  ELSE IF @input = 'B' 
    INSERT INTO @ret_table VALUES ('Bob')
  ELSE
    INSERT INTO @ret_table VALUES ('Foo'), ('Bar')
RETURN
END;


我的预期结果是在 table2 中插入数据，如下所示:


My expected result is to insert data in table2 like the following:

table2

| ID |  NAME |
|----|-------|
|  1 | Alice |
|  2 |   Bob |
|  3 |   Foo |
|  3 |   Bar |


为了实现这一点，我尝试了一些 CTE(公用表表达式)和关系查询，但都没有按预期工作.到目前为止，我得到的唯一可行的解​​决方案是迭代而非执行的解决方案.


To achieve this, I've tried some CTEs (Common Table Expression) and relational queries, but none worked as desired. The only working solution that I've got so far was an iterative and not performatic solution.

我目前的工作解决方案:

BEGIN
  DECLARE 
    @ID int, 
    @i int = 0,
    @max int = (SELECT COUNT(name) FROM table1)

  WHILE ( @i < @max ) -- In this example, it will iterate 3 times
  BEGIN

    SET @i += 1

    -- Select table1.ID where row_number() = @i
    SET @ID =
          (SELECT 
            id 
          FROM
            (SELECT 
              id,
              ROW_NUMBER() OVER (ORDER BY id) as rn
            FROM 
              table1) rows
          WHERE
              rows.rn = @i
          )

    -- Insert into table2 one or more rows related with table1.ID
    INSERT INTO table2
      (id, name)
    SELECT
      @ID,
      fn_result.output
    FROM
      fn_myExampleFunction (
        (SELECT name FROM table1 WHERE id = @ID)
      ) fn_result

    END
END


目标是在不迭代 ID 的情况下实现相同的目标.

推荐答案

如果问题是关于如何以面向集合的方式应用一个函数，那么cross apply(或outer apply) 是你的朋友:

if the question is about how to apply a function in a set oriented way, then cross apply (or outer apply) is your friend:

insert into table2 (
    id, name
) select
   t1.id,
   t2.output
from
   table1 t1
       cross apply
   fn_myExampleFunction(t1.name) t2

示例 SQLFiddle

如果您的函数的非简化版本可以重写，那么其他解决方案可能会更快.

If the non-simplified version of your function is amenable to rewriting, the other solutions will likely be faster.

相关文章