Sql - 嵌套循环 [英] Sql - nested looping

问题描述

我有一张桌子



表A

--------

A

B

C

D



我需要一个结果表格下面



表A

--------

A

B

C

D

AB

BC

CD

ABC

BCD

ABCD





任意帮助赞赏。



我尝试了什么：



我试过带来两个单词的组合。

I am having a table

Table A
--------
A
B
C
D

I need a result table as below

Table A
--------
A
B
C
D
AB
BC
CD
ABC
BCD
ABCD


Any help appreciated.

What I have tried:

I tried bringing two words combination.

DECLARE @tbl_wordlist TABLE (
    [Id]        int identity,
    [Word]   nvarchar(max)
)
INSERT INTO @tbl_wordlist
SELECT [Data] FROM Split('A B C D', ' ')

-- LOGIC :::::: USING WHILE LOOP, GROUP TWO-TWO WORDS AND THEN INSERT INTO THE SAME TABLE
DECLARE @MaxCount INTEGER
DECLARE @Count INTEGER, @I INT
DECLARE @Txt VARCHAR(MAX)
SET @Count = 1
SET @I = 2
SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Count AND [Word] IS NOT NULL)
SET @MaxCount = (SELECT MAX(ID) FROM @tbl_wordlist) 
WHILE @Count<@MaxCount
    BEGIN
    IF @Txt!=''
        SET @Txt=@Txt+' ' + (SELECT [Word] FROM @tbl_wordlist WHERE ID = (@I))
    ELSE
        SET @Txt=(SELECT [Word] FROM @tbl_wordlist WHERE ID=@Count)
    SET @Count=@Count+1
    SET @I=@I+1
    INSERT INTO @tbl_wordlist([Word])Values(@Txt)
    SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Count AND [Word] IS NOT NULL)
    END

SELECT * FROM @tbl_wordlist;

推荐答案

我知道你已经接受了答案但是我昨天说，不需要使用循环，实际上你需要在SQL中使用任何循环的次数非常少 - 因为它是基于SET的。



产生输出的一种非常简单的方法就是使用一系列产品自联...

I know you have already accepted an answer but as I said yesterday, there is no need to use a loop, in fact the number of times you need to use any loop in SQL is very rare - because it is SET based.

A very simple way of producing your output is just to use a series of self-joins...

-- Get the AB, BC, CD entries with a self join
INSERT INTO @tbl_wordlist 
SELECT A.Word + B.Word FROM @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 1 = B.Id
WHERE B.Word IS NOT NULL

-- Get the ABC, BCD entries with another self join
INSERT INTO @tbl_wordlist 
select A.Word + B.Word
from @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 5 = B.Id	-- NB The 5 here is important!
WHERE B.Word IS NOT NULL

-- Get the ABCD entry with yet another self join
INSERT INTO @tbl_wordlist 
select A.Word + B.Word
from @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 8 = B.Id	-- NB The 8 here is also important!
WHERE B.Word IS NOT NULL

还有其他更好的方法可以使用，这只是第一个想到的方法。



如果这是你的家庭作业然后你的导师可能会开始教你更多关于组合很快，他们绝对不需要你在SQL中保持编码循环（如果他们那么离开那个大学！）。我对循环的使用非常热情，我写了一篇关于它的文章 - 处理SQL Server中的循环 [ ^ ] - 我的文章只涉及SQL中可以完成的工作（应该完成）的表面，而不会接近传统的循环所以做更多的研究。

There are other and better approaches that could be used, this is just the first one that came to mind.

If this is your homework then your tutor is probably going to start teaching you more about combinations soon, and they are absolutely not going to need you to keep coding loops in SQL (if they do then leave that college!). I got so passionate about loops being used I wrote an article about it - Processing Loops in SQL Server[^] - my article only touches the surface of what can be done (what should be done) in SQL without going anywhere near a conventional "loop" so do do more research.

修改，请尝试

Modified, please try

DECLARE @tbl_wordlist TABLE (
    [Id]        int identity,
    [Word]   nvarchar(max)
)
INSERT INTO @tbl_wordlist
SELECT [Data] FROM Split--('A B C D', ' ')
 
DECLARE @COUNTER AS INTEGER

DECLARE @RUNNINGCOUNTER AS INTEGER 
DECLARE @NUMBEROFRUNS AS INTEGER = 1

--DECLARE @INNERCOUNTER AS INTEGER
DECLARE @TOTAL AS INTEGER  = (SELECT COUNT(*) FROM SPLIT)

SET @RUNNINGCOUNTER = (SELECT COUNT(*) FROM SPLIT)
sET @COUNTER = 1

DECLARE @WORD AS VARCHAR(MAX)

WHILE @RUNNINGCOUNTER > 0
BEGIN
	SET @NUMBEROFRUNS = 1
	WHILE @NUMBEROFRUNS <= @RUNNINGCOUNTER -1
	BEGIN

		SET @WORD = ''		
		SET @COUNTER = 1
		SET @WORD =''
		
		--PRINT 'RUN Counter '  + cast (@NUMBEROFRUNS as nvarchar(10))
		
		WHILE @COUNTER <= @TOTAL - (@RUNNINGCOUNTER -2)
		BEGIN
		--PRINT 'Counter '  + cast (@cOUNTER as nvarchar(10))
			SET @WORD = @WORD  + (SELECT WORD FROM @tbl_wordlist 
			WHERE ID =@NUMBEROFRUNS -1 + @COUNTER)
			SET @COUNTER=@COUNTER+1
		END
	
		pRINT @WORD
		INSERT INTO @tbl_wordlist (WORD) VALUES(@WORD)	
		
		
		SET @NUMBEROFRUNS =@NUMBEROFRUNS +1
	END
	
	SET @NUMBEROFRUNS = 1
	SET @RUNNINGCOUNTER = @RUNNINGCOUNTER -1
END
sELECT * FROM @tbl_wordlist

这篇关于Sql - 嵌套循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！