我设法编写了一个查询，该查询可以得到我想要的东西，但是它并不像我想要的那样容易阅读。
--Builds a list of all combinations of classesSELECt [ClassID], [ClassName] INTO #classnames FROM [Glad1].[dbo].[Classes] SELECt a.classname AS Class1, a.ClassID AS Class1ID, b.classname AS Class2, b.ClassID AS Class2ID INTO #combos FROM #classnames a cross join #classnames bSELECt Classes.ClassName,COUNT(*) AS SkillCount INTO #skillcounts FROM [Glad1].[dbo].[ClassSkills]RIGHT JOIN [Glad1].[dbo].[Classes]ON ClassSkills.ClassID=Classes.ClassIDGROUP BY ClassName--Finds the percent overlap for each class combinationSELECt ClassOne, ClassTwo,CAST(ROUND(((SharedSkills * 2.0) / (sc1.skillCount + sc2.SkillCount) * 100.0),0) AS DECIMAL(8,0)) AS PercentOverlap INTO #percentoverlaps FROM(SELECt cn1.ClassName AS ClassOne, cn2.ClassName AS ClassTwo, SharedSkills FROM #classnames cn1 JOIN(SELECt Class1ID, Class2ID, Count(Class1Skills.[SkillID]) AS SharedSkills FROM(SELECt DISTINCT [Class1ID],[SkillID] FROM [Glad1].[dbo].[ClassSkills] JOIN #combos ON #combos.Class1ID = ClassSkills.ClassID) Class1SkillsINNER JOIN(SELECt DISTINCT [Class2ID], [SkillID] FROM [Glad1].[dbo].[ClassSkills] JOIN #combos ON #combos.Class2ID = ClassSkills.ClassID) CLass2SkillsON Class1Skills.[SkillID] = Class2Skills.[SkillID]Group by Class1ID, CLass2ID) AllSharedSkillsON cn1.ClassID = Class1IDJOIN #classnames cn2ON cn2.ClassID = Class2ID) NamedJOIN #skillcounts sc1ON sc1.ClassName = ClassoneJOIN #skillcounts sc2ON sc2.ClassName = ClassTwo--Dynamically builds the columns to turn the results into a matrix of percent overlapDECLARE @cols AS NVARCHAr(MAX), @query AS NVARCHAr(MAX);select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ClassTwo) FROM #percentoverlaps c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)') ,1,1,'')set @query = 'SELECt ClassOne, ' + @cols + ' from ( select ClassOne, ClassTwo, PercentOverlap from #percentoverlaps) x pivot ( max(PercentOverlap) for ClassTwo in (' + @cols + ') ) p 'execute(@query)--cleans up temp tablesDROP TABLE #classnamesDROP TABLE #combosDROP TABLE #skillcountsDROP TABLE #percentoverlaps
