45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
WITH uma_names AS (
|
|
SELECT
|
|
"index" AS "id",
|
|
"text" AS "name"
|
|
FROM text_data
|
|
WHERE category = 6 AND "index" BETWEEN 1000 AND 1999
|
|
-- Exclude characters who have no succession relations defined.
|
|
AND "index" IN (SELECT chara_id FROM succession_relation_member)
|
|
), pairs AS (
|
|
SELECT
|
|
a.id AS id_a,
|
|
a.name AS name_a,
|
|
b.id AS id_b,
|
|
b.name AS name_b
|
|
FROM uma_names a
|
|
JOIN uma_names b ON a.id != b.id -- exclude reflexive cases
|
|
), relation_pairs AS (
|
|
SELECT
|
|
ra.relation_type,
|
|
ra.chara_id AS id_a,
|
|
rb.chara_id AS id_b
|
|
FROM succession_relation_member ra
|
|
JOIN succession_relation_member rb ON ra.relation_type = rb.relation_type
|
|
), affinity AS (
|
|
SELECT
|
|
pairs.*,
|
|
SUM(IFNULL(relation_point, 0)) AS base_affinity
|
|
FROM pairs
|
|
LEFT JOIN relation_pairs rp ON pairs.id_a = rp.id_a AND pairs.id_b = rp.id_b
|
|
LEFT JOIN succession_relation sr ON rp.relation_type = sr.relation_type
|
|
GROUP BY pairs.id_a, pairs.id_b
|
|
|
|
UNION ALL
|
|
-- Reflexive cases.
|
|
SELECT
|
|
uma_names.id AS id_a,
|
|
uma_names.name AS name_a,
|
|
uma_names.id AS id_b,
|
|
uma_names.name AS name_b,
|
|
0 AS base_affinity
|
|
FROM uma_names
|
|
)
|
|
SELECT * FROM affinity
|
|
ORDER BY id_a, id_b
|