Je veux faire une auto-jointure externe complète qui inclut des valeurs nulles. Par exemple, si la table Data ressemble à:
N Name Val
--------------
1 ABC 8
1 DEF 7
2 ABC 9
2 XYZ 6
(où N est une colonne d'index générale pour activer une auto-jointure sur des groupes séquentiels) et je fais:
SELECT COALESCE(a.n, b.n) as n, COALESCE(a.Name, b.Name) as Name, a.Val as A, b.Val as B
FROM Data a
FULL OUTER JOIN Data b on a.N = b.N - 1 and a.Name = b.Name
Je voudrais:
N Name A B
---------------
1 ABC 8 9
1 DEF 7 NULL
1 XYZ NULL 6
Mais ce que j'obtiens est plus comme une jointure croisée:
n Name A B
--------------
1 ABC 8 9
1 DEF 7 NULL
2 ABC 9 NULL
2 XYZ 6 NULL
1 ABC NULL 8
1 DEF NULL 7
2 XYZ NULL 6
Comment effectuer cette jointure externe complète afin d'obtenir les résultats condensés de l'auto-jointure?
( Remarque: En pratique, la colonne N est un index généralisé, les solutions qui nécessitent de nommer les valeurs de N ne sont donc pas pratiques.)
4 réponses
Puisque nous voulons gérer une colonne d'index d'auto-jointure généralisée N, étendons un peu plus l'ensemble d'échantillons:
create table #Data (n int, name char(3), val int)
insert into #Data values (1, 'ABC',8)
insert into #Data values (1, 'DEF',7)
insert into #Data values (2, 'ABC',9)
insert into #Data values (2, 'XYZ',6)
insert into #Data values (3, 'ABC',9)
insert into #Data values (3, 'DEF',5)
insert into #Data values (3, 'XYZ',4)
Pour cet exemple, nous voulons que le SQL produise cette sortie:
N Name A B
---------------
1 ABC 8 9
1 DEF 7 NULL
1 XYZ NULL 6
2 ABC 9 9
2 DEF NULL 5
2 XYZ 6 4
Le code suivant fonctionne sur le cas général:
SELECT COALESCE(a.n, b.n-1) as i, COALESCE(a.Name, b.Name) as Name, a.Val as A, b.Val as B
FROM #Data a
FULL OUTER JOIN #Data b ON a.N = b.N - 1 AND a.Name = b.Name
WHERE a.n < (SELECT MAX(n) FROM #Data) -- Deals with end index case
OR (a.n is null AND b.n-1 IN (SELECT DISTINCT n FROM #Data))
ORDER BY COALESCE(a.n, b.n-1), Name
Pour voir pourquoi cela fonctionne, une bonne étape intermédiaire est de noter que lorsque a.N = 1 nous voulons les lignes d'où n = 1:
SELECT COALESCE(a.n, b.n - 1) as n, COALESCE(a.Name, b.Name) as Name,
a.Val as A, b.Val as B
FROM #Data a
FULL OUTER JOIN #Data b ON a.N = b.N - 1 AND a.Name = b.Name
Veuillez consulter le code ci-dessous:
create table Data (n int, name char(3), val int)
insert into data values (1, 'ABC',8)
insert into data values (1, 'DEF', 7)
insert into data values (2 , 'ABC' , 9)
insert into data values (2 , 'XYZ', 6)
SELECT COALESCE(a.Name, b.Name) as Name, a.Val as A, b.Val as B
FROM Data a
FULL OUTER JOIN Data b on a.N = b.N - 1 and a.Name = b.Name
La sortie est la suivante:
Il y a des valeurs nulles des deux côtés.
Peut être ça:
SELECT [Name]
,[1]
,[2]
FROM [table]
PIVOT
(
MAX([val]) FOR [N] IN ([1], [2])
) PVT;
Jusqu'à présent, je n'ai pu voir cela que comme un syndicat. et une jointure gauche et droite puisque les critères de ce que vous recherchez changent.
SELECT COALESCE(a.Name, b.Name) as Name, a.Val as A, b.Val as B
FROM Data a
LEFT JOIN Data b on a.Name = b.Name
and B.N = 2
WHERE A.N = 1
UNION
SELECT COALESCE(a.Name, b.Name) as Name, a.Val as A, b.Val as B
FROM Data a
RIGHT JOIN Data b on a.Name = b.Name
and A.N = 1
WHERE B.N = 2
Nous donnant:
+------+---+----+
| NAME | A | B |
+------+---+----+
| ABC | 8 | 9 |
| DEF | 7 | |
| XYZ | | 6 |
+------+---+----+
Cependant, cela repose sur une valeur N codée en dur qui, à mon avis, n'est pas si utile ... travailler mieux.