我设法编写了一个查询来获取我想要的内容,但并不像我想要的那么容易阅读。
--Builds a list of all combinations of classes
SELECT [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 b
SELECT Classes.ClassName,COUNT(*) AS SkillCount INTO #skillcounts FROM [Glad1].[dbo].[ClassSkills]
RIGHT JOIN [Glad1].[dbo].[Classes]
ON ClassSkills.ClassID=Classes.ClassID
GROUP BY ClassName
--Finds the percent overlap for each class combination
SELECT 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) Class1Skills
INNER JOIN
(SELECT DISTINCT [Class2ID], [SkillID] FROM [Glad1].[dbo].[ClassSkills]
JOIN #combos ON #combos.Class2ID = ClassSkills.ClassID) CLass2Skills
ON Class1Skills.[SkillID] = Class2Skills.[SkillID]
Group by Class1ID, CLass2ID
) AllSharedSkills
ON cn1.ClassID = Class1ID
JOIN #classnames cn2
ON cn2.ClassID = Class2ID) Named
JOIN #skillcounts sc1
ON sc1.ClassName = ClassOne
JOIN #skillcounts sc2
ON sc2.ClassName = ClassTwo
--Dynamically builds the columns to turn the results into a matrix of percent overlap
DECLARE @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 tables
DROP TABLE #classnames
DROP TABLE #combos
DROP TABLE #skillcounts
DROP TABLE #percentoverlaps