Как запросить несколько таблиц и использовать дополнительные
У меня есть несколько таблиц, чтобы составить таблицу из запроса, упорядоченного по итоговой оценке.
Я могу сделать это, используя пары запросов и php, чтобы это произошло, но я не знаю, как сделать заказ по final_score DESC.
Я использую MySQL 5.6 и PHP
Пожалуйста помоги.
Существующие таблицы
столовая песерта
| id_peserta | имя |
| 1 | Мистер. один |
| 2 | Мистер. два |
стол нилай
| id_nilai | id_peserta | nilai_a | nilai_b | nilai_c |
| 1 | 1 | 70 | 80 | 90 |
| 2 | 2 | 85 | 95 | 75 |
столовый центр
| id_center | имя_центра |
| 1 | центр1 |
| 2 | центр2 |
| 3 | центр3 |
настольный очерк
| id_essay | id_center | id_peserta | nilai_e |
| 1 | 1 | 1 | 65 |
| 2 | 2 | 1 | 60 |
| 3 | 3 | 1 | 50 |
| 4 | 1 | 2 | 55 |
Мне нужно что-то вроде этого:
| id_peserta | имя | центр1 | центр2 | центр3 | total_center | nilai_a | nilai_b | nilai_c | total_nilai | final_score | прошло / не удалось |
| 1 | Мистер. один | 65 | 60 | 50 | 175 = 65 + 60 + 50 | 70 | 80 | 90 | 250 = 70 + 80 + 90 | 425 = 175 + 250 | прошло |
| 2 | Мистер. два | 55 | | | 55 | 85 | 75 | 95 | 265 = 85 + 75 + 95 | 320 = 55 + 265 | не удалось |
упорядочить по final_score
Решение
В качестве отправной точки:
select n.id_peserta, n_sum+coalesce(e_sum,0) as final_score, center_count from
(select id_peserta, sum(nilai_a+nilai_b+nilai_c) as n_sum from nilai
group by id_peserta, nilai_a+nilai_b+nilai_c) n
left join
(select id_peserta, sum(nilai_e) as e_sum, count(1) as center_count from essay
group by id_peserta) e
on n.id_peserta = e.id_peserta
order by n_sum+coalesce(e_sum,0) DESC
Число «nilai» кажется статическим (как уже часть структуры таблицы). Но количество центров рассчитано на динамичность. Поэтому я не стал бы «перекрестную таблицу», а вместо этого показал бы количество посещенных центров.
Может быть, стоит оспорить проект модели данных, но выше следует дать вам желаемый ответ на основе данной структуры.
Рабочее предположение: nilai.id_peserta — это уникальный ключ.
С уважением
Ральф
(написано в текстовом редакторе без тестирования, поэтому вы можете столкнуться с опечаткой)
Другие решения
Разделите результаты двух частей.
первая часть
SELECT p.id_peserta, p.name, n.nilai_a, n.nilai_b, n.nilai_c,
(n.nilai_a+n.nilai_b+n.nilai_c) as total_nilai
FROM peserta p LEFT JOIN nilai n ON p.id_peserta=n.id_nilai
Вторая часть
SELECT e.id_peserta, SUM(IF(c.center_name='center1', nilai_e, 0)) as center1 ,
SUM(IF(c.center_name='center2', nilai_e, 0)) as center2 ,
SUM(IF(c.center_name='center3', nilai_e, 0)) as center3 ,SUM(e.nilai_e) AS total_center
FROM essay e LEFT JOIN center c ON e.id_center=c.id_center GROUP BY e.id_peserta
а затем объединить
SELECT t1.*, t2.center1, t2.center2, t2.center3, t2.total_center,
(t1.total_nilai+t2.total_center) as final_score, IF((t1.total_nilai+t2.total_center)>400, 'passed', 'failed') as `passed/failed`
FROM(
((SELECT p.id_peserta, p.name, n.nilai_a, n.nilai_b, n.nilai_c,
(n.nilai_a+n.nilai_b+n.nilai_c) as total_nilai
FROM peserta p LEFT JOIN nilai n ON p.id_peserta=n.id_nilai)) t1,
(SELECT e.id_peserta, SUM(IF(c.center_name='center1', nilai_e, 0)) as center1 ,
SUM(IF(c.center_name='center2', nilai_e, 0)) as center2 ,
SUM(IF(c.center_name='center3', nilai_e, 0)) as center3 ,SUM(e.nilai_e) AS total_center
FROM essay e LEFT JOIN center c ON e.id_center=c.id_center GROUP BY e.id_peserta) t2)
WHERE t1.id_peserta=t2.id_peserta
ORDER BY final_score DESC;