18 lines
708 B
SQL
18 lines
708 B
SQL
WITH skill_groups AS (
|
|
SELECT DISTINCT group_id FROM skill_data
|
|
)
|
|
SELECT
|
|
g.group_id,
|
|
COALESCE(inh_from.id, s1.id, 0) AS skill1,
|
|
COALESCE(inh_to.id, s2.id, 0) AS skill2,
|
|
IFNULL(s3.id, 0) AS skill3,
|
|
IFNULL(m1.id, 0) AS skill_bad
|
|
FROM skill_groups g
|
|
LEFT JOIN skill_data s1 ON g.group_id = s1.group_id AND s1.group_rate = 1
|
|
LEFT JOIN skill_data s2 ON g.group_id = s2.group_id AND s2.group_rate = 2
|
|
LEFT JOIN skill_data s3 ON g.group_id = s3.group_id AND s3.group_rate = 3
|
|
LEFT JOIN skill_data m1 ON g.group_id = m1.group_id AND m1.group_rate = -1
|
|
LEFT JOIN skill_data inh_to ON s1.id = inh_to.unique_skill_id_1
|
|
LEFT JOIN skill_data inh_from ON s2.unique_skill_id_1 = inh_from.id
|
|
ORDER BY g.group_id
|