-
Notifications
You must be signed in to change notification settings - Fork 1
SQL recipes
DREVET Olivier edited this page Jun 29, 2023
·
1 revision
SELECT
entry.id AS entry_id,
sense.id AS sense_id,
(
SELECT
GROUP_CONCAT(IFNULL(keb || ':', '') || reb)
FROM
r_ele r_ele_sub
LEFT JOIN r_ele_k_ele ON r_ele_k_ele.id_r_ele = r_ele_sub.id
LEFT JOIN k_ele k_ele_sub ON r_ele_k_ele.id_k_ele = k_ele_sub.id
WHERE
r_ele_sub.id_entry = entry.id
) keb_reb_group,
GROUP_CONCAT(DISTINCT gloss.content) gloss_group,
GROUP_CONCAT(DISTINCT pos.name) pos_group,
GROUP_CONCAT(DISTINCT dial.name) dial_group,
GROUP_CONCAT(DISTINCT misc.name) misc_group,
GROUP_CONCAT(DISTINCT field.name) field_group
FROM
entry
JOIN sense ON sense.id_entry = entry.id
JOIN gloss ON gloss.id_sense = sense.id
LEFT JOIN sense_pos ON sense.id = sense_pos.id_sense
LEFT JOIN pos ON sense_pos.id_pos = pos.id
LEFT JOIN sense_dial ON sense.id = sense_dial.id_sense
LEFT JOIN dial ON sense_dial.id_dial = dial.id
LEFT JOIN sense_misc ON sense.id = sense_misc.id_sense
LEFT JOIN misc ON sense_misc.id_misc = misc.id
LEFT JOIN sense_field ON sense.id = sense_field.id_sense
LEFT JOIN field ON sense_field.id_field = field.id
WHERE
entry.id IN (
SELECT
sense.id_entry
FROM
sense
JOIN gloss ON gloss.id_sense = sense.id
WHERE
gloss.content = 'test'
)
GROUP BY
sense.id;
SELECT
k_ele.id k_ele_id,
entry.id AS entry_id,
sense.id AS sense_id,
(
SELECT
GROUP_CONCAT(IFNULL(keb || ':', '') || reb)
FROM
r_ele r_ele_sub
LEFT JOIN r_ele_k_ele ON r_ele_k_ele.id_r_ele = r_ele_sub.id
LEFT JOIN k_ele k_ele_sub ON r_ele_k_ele.id_k_ele = k_ele_sub.id
WHERE
r_ele_sub.id_entry = entry.id
) keb_reb_group,
GROUP_CONCAT(DISTINCT gloss.content) gloss_group,
GROUP_CONCAT(DISTINCT pos.name) pos_group,
GROUP_CONCAT(DISTINCT dial.name) dial_group,
GROUP_CONCAT(DISTINCT misc.name) misc_group,
GROUP_CONCAT(DISTINCT field.name) field_group
FROM
entry
JOIN sense ON sense.id_entry = entry.id
JOIN gloss ON gloss.id_sense = sense.id
LEFT JOIN sense_pos ON sense.id = sense_pos.id_sense
LEFT JOIN pos ON sense_pos.id_pos = pos.id
LEFT JOIN sense_dial ON sense.id = sense_dial.id_sense
LEFT JOIN dial ON sense_dial.id_dial = dial.id
LEFT JOIN sense_misc ON sense.id = sense_misc.id_sense
LEFT JOIN misc ON sense_misc.id_misc = misc.id
LEFT JOIN sense_field ON sense.id = sense_field.id_sense
LEFT JOIN field ON sense_field.id_field = field.id
JOIN r_ele ON entry.id = r_ele.id_entry
LEFT JOIN k_ele ON entry.id = k_ele.id_entry
WHERE
reb = 'リョクトウ'
GROUP BY
sense.id;
- Radicals, on and kun readings and english meanings for a given kanji
SELECT character.*,
GROUP_CONCAT(DISTINCT character_radical.id_radical) as radicals,
GROUP_CONCAT(DISTINCT on_yomi.reading) AS on_reading,
GROUP_CONCAT(DISTINCT kun_yomi.reading) AS kun_reading,
GROUP_CONCAT(DISTINCT meaning.content) AS meanings
FROM character
LEFT JOIN character_radical ON character.id = character_radical.id_character
LEFT JOIN on_yomi ON character.id = on_yomi.id_character
LEFT JOIN kun_yomi ON kun_yomi.id_character = character.id
LEFT JOIN meaning ON meaning.id_character = character.id
WHERE character.id="多";
- List all radicals
radical is a view of the kanji table, so we can have as many info on radicals as kanji.
For example, we can order by stroke
SELECT * FROM radical ORDER BY stroke_count;
- Get radical from kanji
For example, get radicals for the 思 kanji
SELECT radical.*
FROM radical
JOIN character_radical ON character_radical.id_radical = radical.id
WHERE character_radical.id_character="思";
- Get Kanji from radicals
For example, get all Kanji where radicals are 二 and 女
For more radicals, intersect with as many wished radicals.
SELECT id
FROM character
WHERE id IN(SELECT id_character FROM character_radical WHERE id_radical = "二" INTERSECT
SELECT id_character FROM character_radical WHERE id_radical = "女"
);