使用SQL中每個字符的值集串列生成值的所有組合

我有一個看起來像這樣的資料集

我想用每個位置的值生成字串的所有組合。

對于這個例子，輸出看起來像

順序并不特別重要。

注意：可以有任意數量的組和每個組的值。

下面的 SQL 設定了示例輸入。

Declare @Table Table 
(
    groupId int, 
    value int
)

Insert Into @Table 
    Select 1,1
    union select 1,2
    union select 1,3
    union select 2,8
    union select 3,5
    union select 3,6

Select 
        * 
    From 
        @Table

uj5u.com熱心網友回復：

您可以為此使用遞回 CTE，不需要動態 SQL

WITH cte AS (
    SELECT
      1 AS Position,
      CAST(Value AS varchar(100)) AS Value
    FROM #YourTable t
    WHERE Position = 1
    
    UNION ALL
    
    SELECT
      cte.Position   1,
      CAST(cte.Value   t.Value AS varchar(100))
    FROM #YourTable t
    JOIN cte ON cte.Position   1 = t.Position
)
SELECT *
FROM cte
WHERE cte.Position = (SELECT TOP 1 t2.Position FROM #YourTable t2 ORDER BY t2.Position DESC);

SQL小提琴

如果存在差距，那么您需要一個更復雜的解決方案：

CREATE FUNCTION dbo.GetNextValues (@gtThanPosition int)
RETURNS TABLE AS RETURN

SELECT TOP (1) WITH TIES
  t.Position,
  t.Value
FROM YourTable t
WHERE t.Position > @gtThanPosition OR @gtThanPosition IS NULL
ORDER BY t.Position;

GO

WITH cte AS (
    SELECT
      t.Position,
      CAST(Value AS varchar(100)) AS Value
    FROM dbo.GetNextValues(NULL) t

    UNION ALL
    
    SELECT
      t.Position,
      CAST(cte.Value   t.Value AS varchar(100)) AS Value
    FROM cte
    CROSS APPLY dbo.GetNextValues(cte.Position) t
)
SELECT Value
FROM cte
WHERE cte.Position = (SELECT TOP 1 t2.Position FROM YourTable t2 ORDER BY t2.Position DESC);

SQL小提琴

uj5u.com熱心網友回復：

由于您不知道組的數量，我懷疑您需要使用動態 SQL。

例如：

DROP TABLE IF EXISTS #TestGroup;

CREATE TABLE #TestGroup
(
    GroupId int,
    Value int
);

INSERT INTO #TestGroup
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 8),
    (3, 5),
    (3, 6)
;

DECLARE @MaxGroup int;
SELECT @MaxGroup = MAX(GroupId) FROM #TestGroup;

DECLARE @sql nvarchar(max) = N'SELECT ';

DECLARE @i int = 1;
WHILE @i <= @MaxGroup
BEGIN
    If @i <> 1 SET @sql = @sql   N', ';
    SET @sql = @sql   FORMATMESSAGE(N'T%i.Value As C%i', @i, @i);
    SET @i = @i   1;
END;

SET @sql = @sql   N' FROM ';
SET @i = 1;
WHILE @i <= @MaxGroup
BEGIN
    If @i <> 1 SET @sql = @sql   N' CROSS JOIN ';
    SET @sql = @sql   FORMATMESSAGE(' (SELECT Value FROM #TestGroup WHERE GroupId = %i) As T%i ', @i, @i);
    SET @i = @i   1;
END;

PRINT @sql;
EXEC(@sql);

DROP TABLE IF EXISTS #TestGroup;

輸出：

SELECT T1.Value As C1, T2.Value As C2, T3.Value As C3 
FROM  (SELECT Value FROM #TestGroup WHERE GroupId = 1) As T1  
CROSS JOIN  (SELECT Value FROM #TestGroup WHERE GroupId = 2) As T2  
CROSS JOIN  (SELECT Value FROM #TestGroup WHERE GroupId = 3) As T3 

uj5u.com熱心網友回復：

我認為這里真正的問題是你有任意數量的組。使用靜態數量的組，您可以執行以下操作：

SELECT CONCAT(YT1.[Value],YT2.[Value],YT3.[Value])
FROM dbo.YourTable YT1
     JOIN dbo.YourTable YT2 ON YT1.Position < YT2.Position
     JOIN dbo.YourTable YT3 ON YT2.Position < YT3.Position;

因此，一種方法是使用動態解決方案。請注意，如果沒有您使用的 SQL Server 版本的詳細資訊，我假設您使用的是完全受支持的版本。我也假設你不能有間隙。例如，如果position 5被省略，但position 6沒有被省略，那么您不會期望05 位數字（position 6而是第 5 位數字）。

這給了你這個，如果我誠實的話，這很混亂，但可以完成作業：

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);
WITH DisctinctPositions AS(
    SELECT DISTINCT
           Position
    FROM dbo.YourTable),
Positions AS(
    SELECT Position,
           LAG(Position) OVER (ORDER BY Position) AS PrevPosition
    FROM DisctinctPositions)
SELECT @SQL = N'SELECT CONCAT('   STRING_AGG(QUOTENAME(CONCAT(N'YT',P.Position))   N'.[Value]',',') WITHIN GROUP (ORDER BY P.Position)   N')'   @CRLF  
              N'FROM dbo.YourTable YT1'   @CRLF  
              STRING_AGG(CASE P.Position WHEN 1 THEN NULL ELSE N'     JOIN dbo.YourTable'   QUOTENAME(CONCAT(N'YT',P.Position))   N' ON '   QUOTENAME(CONCAT(N'YT',P.PrevPosition))   N'.Position < '   QUOTENAME(CONCAT(N'YT',P.Position))   N'.Position' END, @CRLF) WITHIN GROUP (ORDER BY P.Position)
FROM Positions P;

--PRINT @SQL; Your best friend

EXEC sys.sp_executesql @SQL;

uj5u.com熱心網友回復：

您可以使用遞回cte：

with cte(c, s) as (
  select 2, cast(value AS varchar(100)) from vals where position = 1
  union all
  select c.c 1, cast(concat(c.s,v.value) as varchar(100)) from cte c join vals v on c.c = v.position
)
select c.s from cte c where c.c = (select top 1 v1.position from vals v1 order by v1.position desc)   1

標籤：sql sql-server 组合

上一篇：使用串列將資料從SQLServer匯入到Pandas資料框中以過濾所需的值

下一篇：在SqlServer中使用“Sys”前綴系統或通用表名是否正常？