Kada sam poslednji put gledao MySQL help, nije postojala rank funkcija, pa onda da napišemo sopstvenu:
Code (sql):
SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank
FROM hierarchy t1, hierarchy t2
WHERE t1.id >= t2.id
AND t1.fromid = t2.fromid
AND t1.toid = t2.toid
GROUP BY t1.id, t1.fromid, t1.toid
Što se tiče TTable, potreban je samo broj ponavljanja (from, to) parova:
Code (sql):
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid
Potrebno je izdvojiti one id iz prve tabele čiji je rank <= broju ponavljanja (from, to) iz TTable:
Code (sql):
SELECT x.id
FROM (SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank
FROM hierarchy t1, hierarchy t2
WHERE t1.id >= t2.id
AND t1.fromid = t2.fromid
AND t1.toid = t2.toid
GROUP BY t1.id, t1.fromid, t1.toid) x, (
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid) y
WHERE x.fromid = y.fromid
AND x.toid = y.toid
AND x.myrank <= y.cnt
Sada je ostalo samo da napravimo i delete:
Code (sql):
DELETE FROM hierarchy WHERE id IN (
SELECT id
FROM (SELECT x.t1.id, t1.fromid, t1.toid, COUNT(*) myrank
FROM hierarchy t1, hierarchy t2
WHERE t1.id >= t2.id
AND t1.fromid = t2.fromid
AND t1.toid = t2.toid
GROUP BY t1.id, t1.fromid, t1.toid) x, (
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid) y
WHERE x.fromid = y.fromid
AND x.toid = y.toid
AND x.myrank <= y.cnt)