MS SQL Server
  1. MS SQL Server
  3. Construire une masortingce de comparaisons dans SQl Server
Intereting Posts
supprimer les lignes sélectionnées dans la table (Comment augmenter la vitesse) SQL TOTAL ET GRAND TOTAL Générer la key primaire autoincrementing varchar dans le server sql Feuille Excel demandant des informations d'identification de connection SQl Comment puis-je créer une procédure Python CLR dans SQL Server? J'ai un problème en essayant de UNION All in SQL Server 2008 sql server 2008 – find les tables contenant un text spécifique Exécute la command Insert et returnne l'identifiant inséré dans Sql Comment get la valeur de la table 'from' dans la partie OUTPUT de la requête SQL Recherche des valeurs en double de lignes où un autre champ a une valeur Ajout de SQL Server Count Modèles d'access à la database de bureau C # SQL Server (LocalDB) Représenter une date avec le minimum de caractères possible en utilisant SQL Query Requête optimisant Comment Agrégation de rangs avec rang ou index> = N

Construire une masortingce de comparaisons dans SQl Server

J'ai une question sur une requête SQL décemment compliquée. (J'utilise sql server) Dans ma database, j'ai un set de classs, et un set de compétences que ces classs ont (dans une relation many-to-many).

Je veux build une requête qui construit une comparaison de masortingce (semblable à celle dans Comment créer une masortingce avec SQL ) Mais sans coder en dur chaque colonne, si possible. J'espère comparer la quantité de chevauchement pour chaque paire de class, en: 

overlap = (Skills_Both_Classes_Have / Total_Skills) * 100

Mon problème principal est comment find le chevauchement de chaque paire de classs d'une manière que je peux modifier / afficher.

mise en forme pour la table de class: 

 |ClassID | ClassName | ------------------------ |1 | Class1 | |2 | Class2 | |3 | Class3 |

formatting pour la table de compétences: 

 |SkillID | SkillName | ------------------------ |1 | Skill1 | |2 | Skill2 | |3 | Skill3 |

formatting pour la table médiane: 

 |ClassID |SkillID| ------------------ |1 | 1 | |1 | 2 | |1 | 3 | |2 | 2 | |2 | 4 | |2 | 5 | |3 | 1 | |3 | 2 | |3 | 5 |

exemple de sortie: 

  |Class1 |Class2 |Class3 | -------------------------------- Class1 | 100 | 033 | 066 | Class2 | 033 | 100 | 066 | Class3 | 066 | 066 | 100 |

J'ai joué avec le pivot et le genre, mais j'ai du mal à faire le tour de la meilleure façon de l'implémenter en SQL.

Dans n'importe quel autre langage, j'utiliserais un couple pour chaque boucle, et j'enverrais la sortie dans un tableau, mais cela ne semble pas être une très bonne solution en SQL. Ce n'est pas pour une mission ou quoi que ce soit, juste pour ma propre curiosité personnelle.

J'ai réussi à écrire une requête qui obtient ce que je veux, mais n'est pas aussi facile à lire que je le voudrais. 

 --Builds a list of all combinations of classs 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 masortingx 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