在 SQL Server 中，如何在 select 中创建 while 循环 [英] In SQL Server, how to create while loop in select

问题描述

数据将是这样的:

    id  |  data
    ----|---------
    1   |  AABBCC
    2   |  FFDD
    3   |  TTHHJJKKLL

我想要的结果是什么

    id  |  data
    ----|---------
    1   |  AA
    1   |  BB
    1   |  CC
    2   |  FF
    2   |  DD
    3   |  TT
    3   |  HH
    3   |  JJ
    3   |  KK
    3   |  LL 

我有一个使用游标的 sql 代码

I have a sql code using cursor

DECLARE @table2 table ( id INTEGER, data VARCHAR(500))

DECLARE Cur CURSOR FOR
SELECT id FROM table1

OPEN Cur 

WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        DECLARE @LoopNum INTEGER
        DECLARE @tempID INTEGER
        DECLARE @tempDATA VARCHAR(255)
        FETCH NEXT FROM Cur INTO @tempID
        SET @tempDATA = SELECT data from table1 where id = @teampID
        SET @LoopNUM = 0
        WHILE @LoopNum< len(@tempDATA) / 2
            BEGIN
            INSERT INTO table2 (id, data)
            VALUES( @tempID, SUBSTRING(@tempDATA, @LoopNum * 2 +1, 2))
            SET @LoopNum = @LoopNum + 1
        END
    END

CLOSE Cur 
DEALLOCATE Cur 

SELECT * FROM table2

我不想用 CURSOR 因为它太慢了，我有很多数据.有没有人对此有好主意?可能吗?

I don't want to use CURSOR because it's too slow, I have many data. Does anyone has good idea for it? Is it possible?

推荐答案

1. 创建将传入字符串(例如AABBCC")解析为字符串表(特别是AA"、BB"、CC")的函数.
2. 从表中选择 ID 并使用 CROSSAPPLY 以data 作为参数的函数，这样您的行数将与当前行的data 中包含的值一样多.不需要游标或存储过程.

1. Create function that parses incoming string (say "AABBCC") as a table of strings (in particular "AA", "BB", "CC").
2. Select IDs from your table and use CROSS APPLY the function with data as argument so you'll have as many rows as values contained in the current row's data. No need of cursors or stored procs.

这篇关于在 SQL Server 中，如何在 select 中创建 while 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！