IT专家使用SQL查询生成时间表的所有可能组合.docx

1、IT专家使用SQL查询生成时间表的所有可能组合本文由我司收集整编，推荐下载，如有疑问，请与我司联系使用 SQL 查询生成时间表的所有可能组合使用 SQL 查询生成时间表的所有可能组合英Generating all possiblecombinations of a timetable using an SQL Query I have an awkward SQL Puzzle that hasbested me.我有一个令人尴尬的 SQL 难题，让我迷惑不解。 I am trying to generate a list of possible configurations of stude

2、nt blocks so I can fittheir course choices into a timetable. A list of possible qualifications and blocks for astudent could be as the following:我正在尝试生成一份学生模块可能配置的列表，这样我就可以把他们的课程选择纳入一个时间表。一个学生可能具备的资格和障碍列表如下: Biology ABiology C Biology D Biology E Chemistry B Chemistry C Chemistry DChemistry E Chemis

3、try F Computing DComputing F Tutorial A Tutorial B Tutorial E Apossible solution of blocks for a student could be对学生来说，积木的一种可能的解决方案是 Biology DChemistry C Computing F Tutorial E How would I query the above datasetto produce all possible combinations of lessons and blocks for a student? I could then p

4、aredown the list removing the ones that clash and choose one that works. I estimate that inthis instance there will be about 120 combinations in total.我如何查询上面的数据集，以生成学生的所有可能的课程和模块组合?然后我可以减少列表，删除那些冲突的，选择一个有效的。我估计在这个例子中总共有 120 种组合。 I could imagine that it would be some kind of cross join. I have tried

5、 all sorts ofsolutions using window functions and cross apply etc but they have all had some kind offlaw. They all tend to get tripped up because each student has a different number ofcourses and each course has a different number of blocks.我可以想象它是某种交叉连接。我用窗口函数和交叉应用等方法尝试过各种解决本文由我司收集整编，推荐下载，如有疑问，请与我司

6、联系方案，但都有一些缺陷。他们都容易出错，因为每个学生有不同数量的课程，每个课程有不同数量的模块。 Cheers for any help you can offer! I can paste in the gnarled mess of a query I have ifnecessary too!为你能提供的任何帮助干杯!如果有必要的话，我也可以把我的查询粘贴到这个混乱的地方! Alex亚历克斯 5For a fixed number of qualifications, the answer is relatively simple - the CROSS JOINoption from

7、 the previous answers will work perfectly.对于固定数量的资格，答案是相对简单的从以前的答案中交叉连接的选项将会非常有效。 However, if the number of qualifications is unknown, or likely to change in the future,hard-coding four CROSS JOIN operations wont work. In this case, the answer gets morecomplicated.然而，如果资格的数量未知，或者将来可能发生变化，硬编码四个交叉连接操作

8、将不起作用。在这种情况下，答案变得更加复杂。 For small numbers of rows, you could use a variation of this answer on DBA, whichuses powers of two and bit comparisons to generate the combinations. However, this willbe limited to a very small number of rows.对于少量的行，您可以在 DBA 上使用这个答案的变体，它使用 2 的幂和比特的比较来生成组合。但是，这将被限制在非常少的行。 For

9、larger numbers of rows, you can use a function to generate every combination ofM numbers from N rows. You can then join this back to a ROW_NUMBER valuecomputed on your source data to get the original row.本文由我司收集整编，推荐下载，如有疑问，请与我司联系对于较大的行数，可以使用一个函数从“N”行生成每个“M”数字的组合。然后，您可以将其与源数据上计算的 ROW_NUMBER 值连接起来，以获

10、得原始的行。 The function to generate the combinations could be written in TSQL, but it would makemore sense to use SQLCLR if possible:生成组合的函数可以用 TSQL 编写，但是如果可能的话，使用 SQLCLR 会更有意义: SqlFunction( DataAccess = DataAccessKind.None, SystemDataAccess =SystemDataAccessKind.None, IsDeterministic = true, IsPrecise

11、= true,FillRowMethodName = “FillRow”, TableDefinition = “CombinationId bigint, Valueint”public static IEnumerable Combinations(SqlInt32 TotalCount, SqlInt32 ItemsToPick)if (TotalCount.IsNull | ItemsToPick.IsNull) yield break; int totalCount =TotalCount.Value; int itemsToPick = ItemsToPick.Value; if

12、(0 = totalCount | 0 =itemsToPick) yield break; long combinationId = 1; var result = new intitemsToPick; varstack = new Stack int stack.Push(0); while (stack.Count 0) int index = stack.Count - 1;int value = stack.Pop(); while (value totalCount) resultindex+ = value+;stack.Push(value); if (index = ite

13、msToPick) for (int i = 0; i result.Length; i+) yieldreturn new KeyValuePair long, int ( combinationId, resulti); combinationId+;break;public static void FillRow(object row, out long CombinationId, out int Value) varpair = (KeyValuePair long, int )row; CombinationId = pair.Key; Value = pair.Value;(Ba

14、sed on this function.)(基于这个函数。) Once the function is in place, generating the list of valid combinations is fairly easy:一旦函数就位，生成有效组合列表就相当容易: DECLARE Blocks TABLE Qualification varchar(10) NOT NULL, Block char(1)NOT NULL, UNIQUE (Qualification, Block)INSERT INTO Blocks VALUES(Biology, A), (Biology,

15、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 = Co

16、unt(DISTINCT Qualification) BlocksWITHcteNumberedBlocks As SELECT ROW_NUMBER() OVER (ORDER BY Qualification,Block) - 1 As RowNumber, Qualification, Block FROM BlockscteAllCombinations AsSELECT C.CombinationId, B.Qualification, B.Block FROM dbo.Combinations(Count,QualificationCount) As C INNER JOIN c

17、teNumberedBlocks As B ON B.RowNumber =C.ValuecteMatchingCombinations As SELECT CombinationId FROMcteAllCombinations GROUP BY CombinationId HAVING Count(DISTINCTQualification) = QualificationCount Count(DISTINCT Block) =QualificationCountSELECT DENSE_RANK() OVER(ORDER BY C.CombinationId)As Combinatio

18、nNumber, C.Qualification, C.Block cteAllCombinations As C INNERJOIN cteMatchingCombinations As MC ON MC.CombinationId =C.CombinationIdORDER BY CombinationNumber, Qualification This query will generatea list of 172 rows representing the 43 valid combinations:该查询将生成一个 172 行的列表，其中包含 43 个有效组合: 1 Biology

19、 A1 Chemistry B1 Computing D1 Tutorial E2 Biology A2 Chemistry B2Computing F2 Tutorial E In case you need the TSQL version of the Combinationsfunction:如果您需要 TSQL 版本的组合函数: CREATE FUNCTION dbo.Combinations TotalCount int, ItemsToPick intReturnsResult TABLE CombinationId bigint NOT NULL, ItemNumber int

20、 NOT NULL,Unique (CombinationId, ItemNumber)BEGINDECLARE CombinationIdbigint;DECLARE StackPointer int, Index int, Value int;DECLARE StackTABLE ID int NOT NULL Primary Key, Value int NOT NULLDECLARE Temp本文由我司收集整编，推荐下载，如有疑问，请与我司联系TABLE ID int NOT NULL Primary Key, Value int NOT NULL Unique SETCombinat

21、ionId = 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; - Pop: SELECT Value = ValueFROM Stack WHERE ID = StackPointer; DELETE FROM Stack WHERE ID =StackPointer; SET StackPointer -= 1; WHIL

22、E Value TotalCount BEGININSERT INTO Temp (ID, Value) VALUES (Index, Value); SET Index += 1;SET Value += 1; - Push: SET StackPointer += 1; INSERT INTO Stack (ID, Value)VALUES (StackPointer, Value); If Index = ItemsToPick BEGIN INSERT INTOResult (CombinationId, ItemNumber) SELECT CombinationId, Value

23、FROMTemp; SET CombinationId += 1; SET Value = TotalCount; END; END; END;Return; Its virtually the same as the SQLCLR version, except for the fact that TSQLdoesnt have stacks or arrays, so Ive had to fake them with table variables.它实际上与 SQLCLR 版本相同，只是 TSQL 没有堆栈或数组，因此我必须用表变量来伪造它们。 That will actually w

24、ork for what you need, where tablea is the biology entries, b ischem, c is computing and d is tutorial. You can specify the joins a bit better:这实际上可以满足你的需要，表 a 是生物元素，b 是化学，c 是计算，d 是教程。您可以更好地指定联接: select * from tablea cross join tableb cross join tablec cross join tabled. Technicallyboth statement ar

25、e the same.this is all cross join so the comma version above is simpler,in more complicated queries, youll want to use the second statement so you can be veryexplicit as to where you are cross joining vs inner/left joins.技术上来说，这两种说法都是一样的这些都是交叉连接，因此上面的逗号版本更简单，在更复杂的查询中，您将希望使用第二个语句，这样您就可以非常清楚地看到交叉连接与内部

26、/左连接的位置。本文由我司收集整编，推荐下载，如有疑问，请与我司联系 You can replace the table entries with a select union statement to give the values youare looking for in query form:您可以用 select union 语句替换“table”条目，以给出查询表单中要查找的值: select * from (select biology as course,a as class union all select biology,cunion all select biology,

27、d union all select biology,e) a cross join(select Chemistryas course,b as class union all select Chemistry,c union all select Chemistry,dunion all select Chemistry,e union all select Chemistry,f) b cross join(selectComputing as course,a as class union all select Computing,c) c cross join(selectTutor

28、ial as course,a as class union all select Tutorial ,b union all select Tutorial,e) d There is your 120 results (4*5*3*2)这是 120 个结果(4*5*3*2) 0Not really seeing the problem, but does this sqlFiddle work?并没有真正看到问题，但是这个 sqlFiddle 吗? 0You should be able to do with a simple union, however each select of t

29、he union wouldhave a filter on only one class type so you dont get BIO, BIO, BIO, BIO, BIO BIO,CHEM, BIO, BIO, BIO etc.您应该可以使用一个简单的 union，但是每个 union 的选择都只有一个类类型的过滤器，因此您不会获得 BIO、BIO、BIO、BIO、BIO、BIO、CHEM、BIO 等等 select b.course as BioCourse, c.course as ChemCourse, co.course as CompCourse,t.course as T

30、utorial from YourTable b, YourTable c, YourTable co, YourTable t whereb.course like Biology% AND c.course like Chemistry% AND co.course likeComputing% AND t.course like Tutorial% 0Lets use the paradigm where Table1 is Biology, Table2 is chemistry, Table3 iscomputing and Table4 is tutorial. Each tabl

31、e has 1 column and that is the possible blocks本文由我司收集整编，推荐下载，如有疑问，请与我司联系for that table or course. To get all possible combinations, we want to Cartesian Product allof the tables together and then filter the rows out that have duplicate letters.让我们使用表 1 是生物学，表 2 是化学，表 3 是计算，表 4 是教程的范例。每个表有一列，这是该表或课程可能的块。为了得到所有可能的组合，我们想要笛卡尔积所有的表，然后过滤出有重复字母的行。 Each column in the end