SQl服务器中比较矩阵的构建
SQl服务器中比较矩阵的构建
提问于 2013-07-30 20:55:45
我有一个关于一个非常复杂的SQL查询的问题。(我正在使用sql服务器)在我的数据库中，我有一组类，以及这些类所具有的一组技能(在多到多的关系中)。
我想构建一个查询，它构建一个矩阵比较(类似于How to create a matrix with SQL) ，但如果可能的话，不对每一列进行硬编码。，我希望通过以下方法来比较每个类对的重叠量：
overlap = (Skills_Both_Classes_Have / Total_Skills) * 100我的主要问题是如何通过修改/显示.的方式找到每一对类的重叠。
类表的格式设置：
|ClassID | ClassName |
------------------------
|1 | Class1 |
|2 | Class2 |
|3 | Class3 |技能表的格式设置：
|SkillID | SkillName |
------------------------
|1 | Skill1 |
|2 | Skill2 |
|3 | Skill3 |中位表的格式：
|ClassID |SkillID|
------------------
|1 | 1 |
|1 | 2 |
|1 | 3 |
|2 | 2 |
|2 | 4 |
|2 | 5 |
|3 | 1 |
|3 | 2 |
|3 | 5 |示例输出：
|Class1 |Class2 |Class3 |
--------------------------------
Class1 | 100 | 033 | 066 |
Class2 | 033 | 100 | 066 |
Class3 | 066 | 066 | 100 |我一直在玩支点之类的游戏，但我很难找到在SQL中实现这一点的最佳方法。
在任何其他语言中，我都会对每个循环使用一对，并将输出发送到数组中，但在SQL中，这似乎不是一个很好的解决方案。这不是为了任务什么的，只是为了我个人的好奇心。
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-08-16 13:31:37
我成功地编写了一个查询，它得到了我想要的东西，但并不像我想要的那样容易阅读。
--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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17957230
复制相关文章
相似问题
腾讯云开发者
