Это слишком долго для комментариев, поэтому я публикую это как ответ. Я хочу отметить, что это статический пример, но я надеюсь, что его можно легко перевести как динамическое утверждение.
Шаги записываются как комментарии в утверждении:
WITH rcte AS
(
-- Recursive query to generate all numbers from 1 to 4
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM rcte
WHERE Number < 4
), permutations AS (
-- All possible permutations with sum equal to 4
-- There is additional column DuplicateMarker.
-- It will be used later, because 0,0,0,4 and 0,4,0,0 are the same
SELECT
t1.Number AS Number1,
t2.Number AS Number2,
t3.Number AS Number3,
t4.Number AS Number4,
CONCAT(LTRIM(STR(t1.Number)), '.', LTRIM(STR(t2.Number)), '.', LTRIM(STR(t3.Number)), '.', LTRIM(STR(t4.Number))) AS DuplicateMarker
FROM rcte t1, rcte t2, rcte t3, rcte t4
WHERE (t1.Number + t2.Number + t3.Number + t4.Number) = 4
), duplicates AS (
-- Get data with splitted DuplicateMarker column
SELECT *
FROM permutations
CROSS APPLY (SELECT [value] FROM STRING_SPLIT(DuplicateMarker, '.')) t
), results AS (
-- Get unique combinations
-- WITHIN GROUP (ORDER BY) will order strings and 0.0.0.4 and 0.4.0.0 will be the same
SELECT DISTINCT STRING_AGG([value], '.') WITHIN GROUP (ORDER BY [value]) AS ScenarioValue
FROM duplicates
GROUP BY Number1, Number2, Number3, Number4
)
SELECT
DENSE_RANK() OVER (ORDER BY r.ScenarioValue) AS ScenarioID,
s.[value]
FROM results r
CROSS APPLY (SELECT [value] FROM STRING_SPLIT(r.ScenarioValue, '.')) s
WHERE [value] <> '0'
Вывод:
ScenarioID value
1 4
2 1
2 3
3 2
3 2
4 1
4 1
4 2
5 1
5 1
5 1
5 1
Обновление:
Благодаря комментарию @ AndriyM я сделал некоторые изменения и теперь вы можете исключить манипуляции со строками:
WITH rcte AS
(
-- Recursive query to generate all numbers from 0 to 4
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM rcte
WHERE Number < 4
), combinations AS (
-- All different combinations with sum equal to 4
SELECT
t1.Number AS Number1,
t2.Number AS Number2,
t3.Number AS Number3,
t4.Number AS Number4,
ROW_NUMBER() OVER (ORDER BY t1.Number, t2.Number, t3.Number, t4.NUmber) AS ScenarioID
FROM rcte t1, rcte t2, rcte t3, rcte t4
WHERE
((t1.Number + t2.Number + t3.Number + t4.Number) = 4) AND
(t1.Number <= t2.Number) AND
(t2.Number <= t3.Number) AND
(t3.Number <= t4.Number)
)
SELECT c.ScenarioID, v.[value]
FROM combinations c
CROSS APPLY (VALUES (c.NUmber1), (c.Number2), (c.Number3), (c.Number4)) AS v ([value])
WHERE v.[value] > 0
Обновление 2:
Подход с использованием динамического оператора - возможно, не лучший подход, но основан на утверждении из первого обновления: [1110 ]
-- Set your @n value
DECLARE @n int
SET @n = 4
-- Declarations
DECLARE @combinationsSelect nvarchar(max)
DECLARE @combinationsRowNumber nvarchar(max)
DECLARE @combinationsFrom nvarchar(max)
DECLARE @combinationsWhere1 nvarchar(max)
DECLARE @combinationsWhere2 nvarchar(max)
DECLARE @combinationsValues nvarchar(max)
SET @combinationsSelect = N''
SET @combinationsRowNumber = N''
SET @combinationsFrom = N''
SET @combinationsValues = N''
SET @combinationsWhere1 = N''
SET @combinationsWhere2 = N''
-- Generate dynamic parts of the statement
;WITH numbers AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < @n
)
SELECT
@combinationsSelect = @combinationsSelect + N', t' + LTRIM(STR(Number)) + N'.Number AS Number' + LTRIM(STR(Number)),
@combinationsRowNumber = @combinationsRowNumber + N', t' + LTRIM(STR(Number)) + N'.Number',
@combinationsValues = @combinationsValues + N', (c.Number' + LTRIM(STR(Number)) + N')',
@combinationsFrom = @combinationsFrom + N', rcte t' + LTRIM(STR(Number)),
@combinationsWhere1 = @combinationsWhere1 + N'+ t' + LTRIM(STR(Number)) + N'.Number ',
@combinationsWhere2 = @combinationsWhere2 +
CASE
WHEN Number = 1 THEN N''
ELSE N'AND (t' + LTRIM(STR(Number-1)) + N'.Number <= t' + + LTRIM(STR(Number)) + N'.Number) '
END
FROM
numbers
SET @combinationsSelect = STUFF(@combinationsSelect, 1, 2, N'')
SET @combinationsRowNumber = STUFF(@combinationsRowNumber, 1, 2, N'')
SET @combinationsValues = STUFF(@combinationsValues, 1, 2, N'')
SET @combinationsFrom = STUFF(@combinationsFrom, 1, 2, N'')
SET @combinationsWhere1 = STUFF(@combinationsWhere1, 1, 2, N'')
SET @combinationsWhere2 = STUFF(@combinationsWhere2, 1, 4, N'')
-- Dynamic statement
DECLARE @stm nvarchar(max)
SET @stm =
N'WITH rcte AS (
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM rcte
WHERE Number < ' + LTRIM(STR(@n)) +
N'), combinations AS (
SELECT ' +
@combinationsSelect +
N', ROW_NUMBER() OVER (ORDER BY ' + @combinationsRowNumber + N') AS ScenarioID
FROM ' + @combinationsFrom +
N' WHERE ((' + @combinationsWhere1 + N') = ' + LTRIM(STR(@n)) + ') AND ' + @combinationsWhere2 +
N')
SELECT c.ScenarioID, v.[value]
FROM combinations c
CROSS APPLY (VALUES ' + @combinationsValues + N') AS v ([value])
WHERE v.[value] > 0'
-- Execute dynamic statement
EXEC (@stm)
Classes are accessed through constants. Classes defined within a module are listed as constants in that module. So you just need to choose the constants that refer to classes.
MyModule.constants.select {|c| MyModule.const_get(c).is_a? Class}
Если Вы находитесь на направляющих, необходимо получить доступ к константам сначала, чтобы они обнаружились, потому что они лениво загружаются.
MyModule::NotAClass = "not a class"
MyModule.constants => [:NotAClass]
MyModule::AClass => :AClass # Access class for it to be autoloaded
MyModule.constants => [:AClass, :NotAClass]
# Now, select the constants which are class instances
MyModule.constants
.map(&MyModule.method(:const_get))
.select { |constant| constant.is_a? Class}
=> [MyModule::AClass]**