Ch strasznie nakombinowalem (mam nadzieje ze ktos nakrzyczy !). Nie udalo mi sie rozwiazac problemu null'a tymczasowo zastapilem 'Z'.
Mamy opisany przypadek
insert into a1 values(1),(2),(3),(3),(3),(4),(5),(6);
insert into a2 values(1),(2),(3),(3),(4),(5);
i rozwiazanie
SET @a=1;
SET @b='Z';
SET @c=1;
SET @d='Z';
SET @c1 = 1;
SET @c2 = 1;
SELECT * FROM
(select v,
@c1:=IF (@c='Z', IF((@c:=a1.v) = a1.v,@d:=1,'ZLE'), IF (@c<>v, IF((@c:=a1.v) = a1.v,@d:=1, "ZLE1"), @d:=@d+1)) AS WAR
from a1 order by a1.v asc) AS T2
LEFT OUTER JOIN
(select v,
@c2:=IF (@b='Z', IF((@b:=a2.v) = a2.v,@a:=1,'ZLE'), IF (@b<>v, IF((@b:=a2.v) = a2.v,@a:=1, "ZLE1"), @a:=@a+1)) AS WAR
from a2 order by a2.v asc) AS T1
ON (T1.v=T2.v) AND (T1.WAR=T2.WAR)
WHERE T1.v IS NULL;
dostajemy
a1-a2
+------+------+------+------+
| v | WAR | v | WAR |
+------+------+------+------+
| 3 | 3 | NULL | |
| 6 | 1 | NULL | |
+------+------+------+------+
znow zalozylem MySQL'a. Nie jest to pelna roznica chcialo by sie FULL OUTER JOINA napisac ale trzeba UNION ....
dodajemy
insert into a2 values(7);
i piszmy z UNION
SET @a=1;
SET @b='Z';
SET @c=1;
SET @d='Z';
SET @c1 = 1;
SET @c2 = 1;
(
SELECT T2.v FROM
(select v,
@c1:=IF (@c='Z', IF((@c:=a1.v) = a1.v,@d:=1,'ZLE'), IF (@c<>v, IF((@c:=a1.v) = a1.v,@d:=1, "ZLE1"), @d:=@d+1)) AS WAR
from a1 order by a1.v asc) AS T2
LEFT OUTER JOIN
(select v,
@c2:=IF (@b='Z', IF((@b:=a2.v) = a2.v,@a:=1,'ZLE'), IF (@b<>v, IF((@b:=a2.v) = a2.v,@a:=1, "ZLE1"), @a:=@a+1)) AS WAR
from a2 order by a2.v asc) AS T1
ON (T1.v=T2.v) AND (T1.WAR=T2.WAR)
WHERE (T1.v IS NULL)
)
UNION
(
SELECT T1.v FROM
(select v,
@c1:=IF (@c='Z', IF((@c:=a1.v) = a1.v,@d:=1,'ZLE'), IF (@c<>v, IF((@c:=a1.v) = a1.v,@d:=1, "ZLE1"), @d:=@d+1)) AS WAR
from a1 order by a1.v asc) AS T2
RIGHT OUTER JOIN
(select v,
@c2:=IF (@b='Z', IF((@b:=a2.v) = a2.v,@a:=1,'ZLE'), IF (@b<>v, IF((@b:=a2.v) = a2.v,@a:=1, "ZLE1"), @a:=@a+1)) AS WAR
from a2 order by a2.v asc) AS T1
ON (T1.v=T2.v) AND (T1.WAR=T2.WAR)
WHERE (T2.v IS NULL)
);
dostajac
+------+
| v |
+------+
| 3 |
| 6 |
| 7 |
+------+
niby OK, ale to potwor ....