MS SQL Server
  1. MS SQL Server
  3. Trouver des lacunes ou des chevauchements dans les plages
Intereting Posts
Optimisation de la requête de procédure stockée SQL pour de meilleures performances Comment puis-je récupérer un nombre défini de caractères qui précèdent et suivent un mot-key? Modifier le paramètre ANSI_NULLS pour toutes les procédures stockées dans la database Meilleure pratique pour PK dans SQL Server Impossible d'extraire la valeur de return (0 ou 1) de la procédure stockée. Impossible d'afficher la table de database SQL sur la page PHP Point décimal supposé dans la conversion de string dans SSIS Est-ce une mauvaise pratique d'utiliser des entiers non signés MySQL? Comment faire pour installer la string de connection dans C # ASP.NET pour SQL Server 2012 Sql Sever Find Chaîne partielle dans une autre string MISE À JOUR ordonnée par les valeurs dans les tables source et de destination SQL Server: split les résultats de GROUP BY en colonnes distinctes Cette requête continue à me donner une erreur de binding mulit-part insertion nestede exec travail autour Comment puis-je vérifier les duplicates avant d'insert dans une table lors de l'insertion par sélectionner

Trouver des lacunes ou des chevauchements dans les plages

Supposons que j'ai une table avec le format suivant: 

| dbo.ROUTES | ---------------------------------------- | ID | ROUTE | LOWER_LIMIT | UPPER_LIMIT | ---------------------------------------- | 0 | A | 0 | 10 | | 1 | B | 11 | 500 | | 2 | C | 600 | 1000 |

Comment puis-je find des plages de numéros qui ne sont pas couvertes par une input de route? Pour l'exemple ci-dessus, je devrais être capable de voir qu'il n'y a pas d'input qui couvre 501 – 599.

Nous utilisons actuellement cette layout, mais avec 4 ou 5 autres colonnes avec différents critères, et nous avons constaté que (comme vous vous en doutez) que la table a grandi et que les seuils inférieur et supérieur ont été mis à jour, nous ' re commencer à voir des vides et des chevauchements.

Je sais que cela se résume vraiment à une mauvaise design, mais tant que nous n'aurons pas les ressources pour l'améliorer, nous pourrions faire quelque chose dans l'intervalle qui pourrait au less nous aider à ranger les tables manuellement.

Merci,

Comme vous ne pouvez pas utiliser les fonctions avance / retard, j'ai utilisé une autre méthode pour y parvenir. Modifier les conditions aux limites dans les colonnes de sortie (missing_val, chevauchement) selon vos besoins en ajoutant / soustrayant 1

Consortingbution: 

 ID LOWER_LIMIT UPPER_LIMIT 0 0 10 1 11 500 2 600 1000 3 980 1100

Sortie: 

 ID LOWER_LIMIT UPPER_LIMIT MISSING_VAL OVERLAPPING 0 0 10 0 0 1 11 500 500-600 0 2 600 1000 0 980-1000 3 980 1100 0 0

Requête 

 SELECT ID, LOWER_LIMIT, UPPER_LIMIT, CASE WHEN UPPER_LIMIT+1=NEXT_LOWER_VAL THEN '0' WHEN UPPER_LIMIT+1< NEXT_LOWER_VAL THEN UPPER_LIMIT||'-'||NEXT_LOWER_VAL ELSE '0' END AS MISSING_VAL, CASE WHEN UPPER_LIMIT+1= NEXT_LOWER_VAL THEN '0' WHEN UPPER_LIMIT+1> NEXT_LOWER_VAL THEN NEXT_LOWER_VAL||'-'||UPPER_LIMIT ELSE '0' END AS OVERLAPPING FROM ( SELECT T1.*, (SELECT MIN(LOWER_LIMIT) FROM TEST_T T WHERE T.ID<> T1.ID AND T.LOWER_LIMIT> T1.LOWER_LIMIT) AS NEXT_LOWER_VAL FROM TEST_T T1) SUB

Cela devrait montrer les lignes qui ont des lacunes de chaque côté de celui-ci: 

 SELECT * FROM ROUTES WHERE NOT Exists(SELECT ID FROM ROUTES as sub WHERE sub.Lower_Limit = ROUTES.Upper_Limit + 1) OR NOT Exists(SELECT ID FROM ROUTES as sub1 WHERE sub1.Upper_Limit = ROUTES.Lower_Limit - 1)

Puisque vous utilisez SQL Server 2008, vous ne pouvez pas utiliser les fonctions LEAD et LAG , vous avez besoin d'une auto-jointure: 

 SELECT T2.UPPER_LIMIT + 1 AS R1 ,T1.LOWER_LIMIT - 1 AS R2 FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RID ,ID ,ROUTE ,LOWER_LIMIT ,UPPER_LIMIT FROM ROUTES) AS T1 LEFT JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RID ,ID ,ROUTE ,LOWER_LIMIT ,UPPER_LIMIT FROM ROUTES) AS T2 ON T1.RID = T2.RID + 1 WHERE T1.LOWER_LIMIT - T2.UPPER_LIMIT > 1;

Dans SQL Server 2012+: 

 SELECT LAG_UPPER_LIMIT + 1 AS R1 ,LOWER_LIMIT - 1 AS R2 FROM (SELECT ID ,ROUTE ,LOWER_LIMIT ,UPPER_LIMIT ,LAG(UPPER_LIMIT) OVER (ORDER BY ID) AS LAG_UPPER_LIMIT FROM ROUTES) AS T WHERE T.LOWER_LIMIT - T.LAG_UPPER_LIMIT > 1

Cela vous donne exactement les plages manquantes.

Ce que vous searchz, ce sont des valeurs upper_limit qui ne chevauchent rien avec la valeur lower_limit suivante. En fait, vous voulez un de plus que la valeur limite supérieure et un de less que la valeur limite inférieure suivante: 

 select r.upper_limit + 1 as missing_lower, (select min(lower_limit) - 1 from routes r3 where r3.lower_limit > r.upper_limit + 1 ) as missing_higher from routes r where not exists (select 1 from routes r2 where r.upper_limit + 1 between r2.lower_limit and r2.upper_limit );