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.)

1
feetwet 17 nov. 2017 à 20:45

4 réponses

Meilleure réponse

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
1
feetwet 17 nov. 2017 à 19:15

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:

enter image description here

Il y a des valeurs nulles des deux côtés.

0
w0051977 17 nov. 2017 à 17:52

Peut être ça:

SELECT [Name]
       ,[1]
       ,[2]
FROM [table]
PIVOT
(
    MAX([val]) FOR [N] IN ([1], [2])
) PVT;
0
gotqn 17 nov. 2017 à 17:55

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.

1
xQbert 17 nov. 2017 à 18:10
47356470