問題描述

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.

這篇關于將迭代查詢更改為基于關系集的查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！