IT专家使用SQL查询生成时间表的所有可能组合.docx
《IT专家使用SQL查询生成时间表的所有可能组合.docx》由会员分享，可在线阅读，更多相关《IT专家使用SQL查询生成时间表的所有可能组合.docx（15页珍藏版）》请在冰豆网上搜索。
IT专家使用SQL查询生成时间表的所有可能组合
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
使用SQL查询生成时间表的所有可能组合
使用SQL查询生成时间表的所有可能组合[英]Generatingallpossible
combinationsofatimetableusinganSQLQueryIhaveanawkwardSQLPuzzlethathas
bestedme.
我有一个令人尴尬的SQL难题，让我迷惑不解。
IamtryingtogeneratealistofpossibleconfigurationsofstudentblockssoIcanfit
theircoursechoicesintoatimetable.Alistofpossiblequalificationsandblocksfora
studentcouldbeasthefollowing:
我正在尝试生成一份学生模块可能配置的列表，这样我就可以把他们的课程选择
纳入一个时间表。
一个学生可能具备的资格和障碍列表如下:
BiologyABiologyCBiologyDBiologyEChemistryBChemistryCChemistryD
ChemistryEChemistryFComputingDComputingFTutorialATutorialBTutorialEA
possiblesolutionofblocksforastudentcouldbe
对学生来说，积木的一种可能的解决方案是
BiologyDChemistryCComputingFTutorialEHowwouldIquerytheabovedataset
toproduceallpossiblecombinationsoflessonsandblocksforastudent?
Icouldthenpare
downthelistremovingtheonesthatclashandchooseonethatworks.Iestimatethatin
thisinstancetherewillbeabout120combinationsintotal.
我如何查询上面的数据集，以生成学生的所有可能的课程和模块组合?
然后我可以
减少列表，删除那些冲突的，选择一个有效的。
我估计在这个例子中总共有120种
组合。
Icouldimaginethatitwouldbesomekindofcrossjoin.Ihavetriedallsortsof
solutionsusingwindowfunctionsandcrossapplyetcbuttheyhaveallhadsomekindof
flaw.Theyalltendtogettrippedupbecauseeachstudenthasadifferentnumberof
coursesandeachcoursehasadifferentnumberofblocks.
我可以想象它是某种交叉连接。
我用窗口函数和交叉应用等方法尝试过各种解决
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
方案，但都有一些缺陷。
他们都容易出错，因为每个学生有不同数量的课程，每个
课程有不同数量的模块。
Cheersforanyhelpyoucanoffer!
IcanpasteinthegnarledmessofaqueryIhaveif
necessarytoo!
为你能提供的任何帮助干杯!
如果有必要的话，我也可以把我的查询粘贴到这个混
乱的地方!
Alex
亚历克斯
5
Forafixednumberofqualifications,theanswerisrelativelysimple-theCROSSJOIN
optionfromthepreviousanswerswillworkperfectly.
对于固定数量的资格，答案是相对简单的——从以前的答案中交叉连接的选项将
会非常有效。
However,ifthenumberofqualificationsisunknown,orlikelytochangeinthefuture,
hard-codingfourCROSSJOINoperationswon’twork.Inthiscase,theanswergetsmore
complicated.
然而，如果资格的数量未知，或者将来可能发生变化，硬编码四个交叉连接操作
将不起作用。
在这种情况下，答案变得更加复杂。
Forsmallnumbersofrows,youcoulduseavariationofthisansweronDBA,which
usespowersoftwoandbitcomparisonstogeneratethecombinations.However,thiswill
belimitedtoaverysmallnumberofrows.
对于少量的行，您可以在DBA上使用这个答案的变体，它使用2的幂和比特的
比较来生成组合。
但是，这将被限制在非常少的行。
Forlargernumbersofrows,youcanuseafunctiontogenerateeverycombinationof
‘M’numbersfrom‘N’rows.YoucanthenjointhisbacktoaROW_NUMBERvalue
computedonyoursourcedatatogettheoriginalrow.
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
对于较大的行数，可以使用一个函数从“N”行生成每个“M”数字的组合。
然后，您
可以将其与源数据上计算的ROW_NUMBER值连接起来，以获得原始的行。
ThefunctiontogeneratethecombinationscouldbewritteninTSQL,butitwouldmake
moresensetouseSQLCLRifpossible:
生成组合的函数可以用TSQL编写，但是如果可能的话，使用SQLCLR会更有意
义:
[SqlFunction（DataAccess=DataAccessKind.None,SystemDataAccess=
SystemDataAccessKind.None,IsDeterministic=true,IsPrecise=true,
FillRowMethodName=“FillRow”,TableDefinition=“CombinationIdbigint,Value
int”publicstaticIEnumerableCombinations（SqlInt32TotalCount,SqlInt32ItemsToPick）
if（TotalCount.IsNull||ItemsToPick.IsNull）yieldbreak;inttotalCount=
TotalCount.Value;intitemsToPick=ItemsToPick.Value;if（0=totalCount||0=
itemsToPick）yieldbreak;longcombinationId=1;varresult=newint[itemsToPick];var
stack=newStackintstack.Push（0）;while（stack.Count0）intindex=stack.Count-1;
intvalue=stack.Pop（）;while（valuetotalCount）result[index++]=value++;
stack.Push（value）;if（index==itemsToPick）for（inti=0;iresult.Length;i++）yield
returnnewKeyValuePairlong,int（combinationId,result[i]）;combinationId++;
break;publicstaticvoidFillRow（objectrow,outlongCombinationId,outintValue）var
pair=（KeyValuePairlong,int）row;CombinationId=pair.Key;Value=pair.Value;
（Basedonthisfunction.）
（基于这个函数。
）
Oncethefunctionisinplace,generatingthelistofvalidcombinationsisfairlyeasy:
一旦函数就位，生成有效组合列表就相当容易:
DECLARE@BlocksTABLEQualificationvarchar（10）NOTNULL,Blockchar
（1）
NOTNULL,UNIQUE（Qualification,Block）INSERTINTO@BlocksVALUES
（‘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@Countint,@QualificationCountint;SELECT@Count=
Count
（1）,@QualificationCount=Count（DISTINCTQualification）@BlocksWITH
cteNumberedBlocksAsSELECTROW_NUMBER（）OVER（ORDERBYQualification,
Block）-1AsRowNumber,Qualification,BlockFROM@BlockscteAllCombinationsAs
SELECTC.CombinationId,B.Qualification,B.BlockFROMdbo.Combinations（@Count,
@QualificationCount）AsCINNERJOINcteNumberedBlocksAsBONB.RowNumber=
C.ValuecteMatchingCombinationsAsSELECTCombinationIdFROM
cteAllCombinationsGROUPBYCombinationIdHAVINGCount（DISTINCT
Qualification）=@QualificationCountCount（DISTINCTBlock）=
@QualificationCountSELECTDENSE_RANK（）OVER（ORDERBYC.CombinationId）
AsCombinationNumber,C.Qualification,C.BlockcteAllCombinationsAsCINNER
JOINcteMatchingCombinationsAsMCONMC.CombinationId=
C.CombinationIdORDERBYCombinationNumber,QualificationThisquerywillgenerate
alistof172rowsrepresentingthe43validcombinations:
该查询将生成一个172行的列表，其中包含43个有效组合:
1BiologyA1ChemistryB1ComputingD1TutorialE2BiologyA2ChemistryB2
ComputingF2TutorialEIncaseyouneedtheTSQLversionoftheCombinations
function:
如果您需要TSQL版本的组合函数:
CREATEFUNCTIONdbo.Combinations@TotalCountint,@ItemsToPickintReturns
@ResultTABLECombinationIdbigintNOTNULL,ItemNumberintNOTNULL,
Unique（CombinationId,ItemNumber）BEGINDECLARE@CombinationId
bigint;DECLARE@StackPointerint,@Indexint,@Valueint;DECLARE@Stack
TABLEIDintNOTNULLPrimaryKey,ValueintNOTNULLDECLARE@Temp
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
TABLEIDintNOTNULLPrimaryKey,ValueintNOTNULLUniqueSET
@CombinationId=1;SET@StackPointer=1;INSERTINTO@Stack（ID,Value）
VALUES（1,0）;WHILE@StackPointer0BEGINSET@Index=@StackPointer-1;
DELETEFROM@TempWHEREID=@Index;--Pop:
SELECT@Value=Value
FROM@StackWHEREID=@StackPointer;DELETEFROM@StackWHEREID=
@StackPointer;SET@StackPointer-=1;WHILE@Value@TotalCountBEGIN
INSERTINTO@Temp（ID,Value）VALUES（@Index,@Value）;SET@Index+=1;
SET@Value+=1;--Push:
SET@StackPointer+=1;INSERTINTO@Stack（ID,Value）
VALUES（@StackPointer,@Value）;If@Index=@ItemsToPickBEGININSERTINTO
@Result（CombinationId,ItemNumber）SELECT@CombinationId,ValueFROM
@Temp;SET@CombinationId+=1;SET@Value=@TotalCount;END;END;END;
Return;It’svirtuallythesameastheSQLCLRversion,exceptforthefactthatTSQL
doesn’thavestacksorarrays,soI’vehadtofakethemwithtablevariables.
它实际上与SQLCLR版本相同，只是TSQL没有堆栈或数组，因此我必须用表变
量来伪造它们。
Thatwillactuallyworkforwhatyouneed,wheretableaisthebiologyentries,bis
chem,ciscomputinganddistutorial.Youcanspecifythejoinsabitbetter:
这实际上可以满足你的需要，表a是生物元素，b是化学，c是计算，d是教程。
您可以更好地指定联接:
select*fromtableacrossjointablebcrossjointableccrossjointabled.Technically
bothstatementarethesame...thisisallcrossjoinsothecommaversionaboveissimpler,
inmorecomplicatedqueries,you’llwanttousethesecondstatementsoyoucanbevery
explicitastowhereyouarecrossjoiningvsinner/leftjoins.
技术上来说，这两种说法都是一样的……这些都是交叉连接，因此上面的逗号版
本更简单，在更复杂的查询中，您将希望使用第二个语句，这样您就可以非常清楚
地看到交叉连接与内部/左连接的位置。
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
Youcanreplacethe‘table’entrieswithaselectunionstatementtogivethevaluesyou
arelookingforinqueryform:
您可以用selectunion语句替换“table”条目，以给出查询表单中要查找的值:
select*from（select‘biology’as‘course’,’a’as‘class’unionallselect‘biology’,’c’
unionallselect‘biology’,’d’unionallselect‘biology’,’e’）acrossjoin（select‘Chemistry’
as‘course’,’b’as‘class’unionallselect‘Chemistry’,’c’unionallselect‘Chemistry’,’d’
unionallselect‘Chemistry’,’e’unionallselect‘Chemistry’,’f’）bcrossjoin（select
‘Computing’as‘course’,’a’as‘class’unionallselect‘Computing’,’c’）ccrossjoin（select
‘Tutorial‘as‘course’,’a’as‘class’unionallselect‘Tutorial‘,’b’unionallselect‘Tutorial
‘,’e’）dThereisyour120results（4*5*3*2）
这是120个结果（4*5*3*2）
0
Notreallyseeingtheproblem,butdoesthissqlFiddlework?
并没有真正看到问题，但是这个sqlFiddle吗?
0
Youshouldbeabletodowithasimpleunion,howevereachselectoftheunionwould
haveafilterononlyoneclasstypesoyoudon’tgetBIO,BIO,BIO,BIO,BIOBIO,
CHEM,BIO,BIO,BIOetc...
您应该可以使用一个简单的union，但是每个union的选择都只有一个类类型的过
滤器，因此您不会获得BIO、BIO、BIO、BIO、BIO、BIO、CHEM、BIO等等……
selectb.courseasBioCourse,c.courseasChemCourse,co.courseasCompCourse,
t.courseasTutorialfromYourTableb,YourTablec,YourTableco,YourTabletwhere
b.courselike‘Biology%’ANDc.courselike‘Chemistry%’ANDco.courselike
‘Computing%’ANDt.courselike‘Tutorial%’0
LetsusetheparadigmwhereTable1isBiology,Table2ischemistry,Table3is
computingandTable4istutorial.Eachtablehas1columnandthatisthepossibleblocks
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
forthattableorcourse.Togetallpossiblecombinations,wewanttoCartesianProductall
ofthetablestogetherandthenfiltertherowsoutthathaveduplicateletters.
让我们使用表1是生物学，表2是化学，表3是计算，表4是教程的范例。
每个
表有一列，这是该表或课程可能的块。
为了得到所有可能的组合，我们想要笛卡尔
积所有的表，然后过滤出有重复字母的行。
Eachcolumnintheend
升级会员 