XdHhfqiJ

1# Создаем ВРЕМЕННУЮ таблицу для конечных документов и причин
2CREATE TEMPORARY TABLE IF NOT EXISTS doc_and_caus (
3    id_incident INT,
4	id INT,
5	id_document INT,
6    id_cause INT,
7    PRIMARY KEY (id_incident), UNIQUE INDEX `id_incident` (id_incident ASC), UNIQUE INDEX `id_document` (id_document ASC), UNIQUE INDEX `id_cause` (id_cause ASC));
8TRUNCATE TABLE doc_and_caus;
9# Загружаем в нее данные
10INSERT INTO doc_and_caus (id_incident, id, id_document, id_cause)
11	SELECT
12	incident.id_incident,
13	death.id,
14	document.id_document,
15	actual_causes.id_cause
16FROM death
17JOIN incident
18USING(id_incident)
19JOIN (
20		SELECT id_incident, MAX(id_type_document) AS `id_type_document`
21		FROM document
22		WHERE id_type_document IN (4, 5, 6, 7, 8, 9, 10, 11)
23		GROUP BY id_incident
24	) AS `actual_documents`
25USING (id_incident)
26	JOIN document
27		ON incident.id_incident = document.id_incident
28		AND actual_documents.id_type_document = document.id_type_document
29	JOIN actual_causes
30		USING(id_document);
31		
32# Загружаем базисные данные - id-шники конечных документов и причин смерти из временной таблицы ДЛЯ свидетельств о СМЕРТИ
33UPDATE statistic_archiv, doc_and_caus
34SET statistic_archiv.id_document=doc_and_caus.id_document,
35	statistic_archiv.id_cause=doc_and_caus.id_cause
36WHERE statistic_archiv.id_incident=doc_and_caus.id_incident;
37# ... ДЛЯ свидетельств о РОЖДЕНИИ
38UPDATE statistic_archiv, document
39SET statistic_archiv.id_document=document.id_document
40WHERE statistic_archiv.id_incident=document.id_incident
41AND document.id_type_document=3;