44 lines
790 B
SQL
44 lines
790 B
SQL
WITH g AS (
|
|
SELECT
|
|
l.id AS l,
|
|
r.id AS r,
|
|
r.group_rate,
|
|
r.rarity
|
|
FROM skill_data l
|
|
JOIN skill_data r ON l.group_id = r.group_id
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
s.id AS l,
|
|
i.id AS r,
|
|
i.group_rate,
|
|
i.rarity
|
|
FROM skill_data s
|
|
JOIN skill_data i ON s.id IN (i.unique_skill_id_1, i.unique_skill_id_2)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
s.id AS l,
|
|
i.id AS r,
|
|
i.group_rate,
|
|
i.rarity
|
|
FROM skill_data s
|
|
JOIN skill_data i ON i.id IN (s.unique_skill_id_1, s.unique_skill_id_2)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
l.id AS l,
|
|
r.id AS r,
|
|
r.group_rate,
|
|
r.rarity
|
|
FROM skill_data i
|
|
JOIN skill_data l ON l.id IN (i.unique_skill_id_1, i.unique_skill_id_2)
|
|
JOIN skill_data r ON r.id IN (i.unique_skill_id_1, i.unique_skill_id_2)
|
|
WHERE l.id != r.id
|
|
)
|
|
SELECT * FROM g
|
|
ORDER BY l, group_rate, rarity DESC
|