对于固定数量的资格证书，答案相对简单 - 先前答案中的CROSS JOIN选项将完美地起作用。
然而，如果资格证书数量未知或可能在未来发生变化，则硬编码四个CROSS JOIN操作将不起作用。在这种情况下，答案变得更加复杂。
对于较少量的行，可以使用DBA上
此答案的变体，该答案使用二的幂和位比较生成组合。但是，这将受到非常少量行的限制。
对于较大量的行，可以使用函数从N行中生成'M'个数字的每个组合。然后，您可以将其与在源数据上计算的ROW_NUMBER值连接，以获取原始行。
生成组合的函数可以用TSQL编写，但如果可能，使用SQLCLR更有意义。
[SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true,
FillRowMethodName = "FillRow",
TableDefinition = "CombinationId bigint, Value int"
)]
public static IEnumerable Combinations(SqlInt32 TotalCount, SqlInt32 ItemsToPick)
{
if (TotalCount.IsNull || ItemsToPick.IsNull) yield break;
int totalCount = TotalCount.Value;
int itemsToPick = ItemsToPick.Value;
if (0 >= totalCount || 0 >= itemsToPick) yield break;
long combinationId = 1;
var result = new int[itemsToPick];
var stack = new Stack<int>();
stack.Push(0);
while (stack.Count > 0)
{
int index = stack.Count - 1;
int value = stack.Pop();
while (value < totalCount)
{
result[index++] = value++;
stack.Push(value);
if (index == itemsToPick)
{
for (int i = 0; i < result.Length; i++)
{
yield return new KeyValuePair<long, int>(
combinationId, result[i]);
}
combinationId++;
break;
}
}
}
}
public static void FillRow(object row, out long CombinationId, out int Value)
{
var pair = (KeyValuePair<long, int>)row;
CombinationId = pair.Key;
Value = pair.Value;
}
(基于此函数。)
一旦函数就位，生成有效组合列表就相当容易：
DECLARE @Blocks TABLE
(
Qualification varchar(10) NOT NULL,
Block char(1) NOT NULL,
UNIQUE (Qualification, Block)
);
INSERT INTO @Blocks
VALUES
('Biology', 'A'),
('Biology', 'C'),
('Biology', 'D'),
('Biology', 'E'),
('Chemistry', 'B'),
('Chemistry', 'C'),
('Chemistry', 'D'),
('Chemistry', 'E'),
('Chemistry', 'F'),
('Computing', 'D'),
('Computing', 'F'),
('Tutorial', 'A'),
('Tutorial', 'B'),
('Tutorial', 'E')
;
DECLARE @Count int, @QualificationCount int;
SELECT
@Count = Count(1),
@QualificationCount = Count(DISTINCT Qualification)
FROM
@Blocks
;
WITH cteNumberedBlocks As
(
SELECT
ROW_NUMBER() OVER (ORDER BY Qualification, Block) - 1 As RowNumber,
Qualification,
Block
FROM
@Blocks
),
cteAllCombinations As
(
SELECT
C.CombinationId,
B.Qualification,
B.Block
FROM
dbo.Combinations(@Count, @QualificationCount) As C
INNER JOIN cteNumberedBlocks As B
ON B.RowNumber = C.Value
),
cteMatchingCombinations As
(
SELECT
CombinationId
FROM
cteAllCombinations
GROUP BY
CombinationId
HAVING
Count(DISTINCT Qualification) = @QualificationCount
And
Count(DISTINCT Block) = @QualificationCount
)
SELECT
DENSE_RANK() OVER(ORDER BY C.CombinationId) As CombinationNumber,
C.Qualification,
C.Block
FROM
cteAllCombinations As C
INNER JOIN cteMatchingCombinations As MC
ON MC.CombinationId = C.CombinationId
ORDER BY
CombinationNumber,
Qualification
;
这个查询将生成一个包含172行的列表，代表了43个有效组合：
1 Biology A
1 Chemistry B
1 Computing D
1 Tutorial E
2 Biology A
2 Chemistry B
2 Computing F
2 Tutorial E
...
如果你需要TSQL版本的Combinations函数：
CREATE FUNCTION dbo.Combinations
(
@TotalCount int,
@ItemsToPick int
)
Returns @Result TABLE
(
CombinationId bigint NOT NULL,
ItemNumber int NOT NULL,
Unique (CombinationId, ItemNumber)
)
As
BEGIN
DECLARE @CombinationId bigint;
DECLARE @StackPointer int, @Index int, @Value int;
DECLARE @Stack TABLE
(
ID int NOT NULL Primary Key,
Value int NOT NULL
);
DECLARE @Temp TABLE
(
ID int NOT NULL Primary Key,
Value int NOT NULL Unique
);
SET @CombinationId = 1;
SET @StackPointer = 1;
INSERT INTO @Stack (ID, Value) VALUES (1, 0);
WHILE @StackPointer > 0
BEGIN
SET @Index = @StackPointer - 1;
DELETE FROM @Temp WHERE ID >= @Index;
SELECT @Value = Value FROM @Stack WHERE ID = @StackPointer;
DELETE FROM @Stack WHERE ID = @StackPointer;
SET @StackPointer -= 1;
WHILE @Value < @TotalCount
BEGIN
INSERT INTO @Temp (ID, Value) VALUES (@Index, @Value);
SET @Index += 1;
SET @Value += 1;
SET @StackPointer += 1;
INSERT INTO @Stack (ID, Value) VALUES (@StackPointer, @Value);
If @Index = @ItemsToPick
BEGIN
INSERT INTO @Result (CombinationId, ItemNumber)
SELECT @CombinationId, Value
FROM @Temp;
SET @CombinationId += 1;
SET @Value = @TotalCount;
END;
END;
END;
Return;
END
这段内容与SQLCLR版本基本相同，唯一的区别是TSQL没有堆栈或数组，因此我不得不使用表变量来模拟它们。
