Блог

Главная - Вопросы по программированию - Объединить 2 запроса, используя объединение с разностным столбцом

Объединить 2 запроса, используя объединение с разностным столбцом

#mysql

#mysql

Вопрос:

У меня есть 2 запроса. Мне нужно объединить это, чтобы получить фактический результат. Вот запрос 1 :

 SELECT a.city as c1, 
sum(c.gps_exam1) as c2, 
sum(c.gps_exam2) as c3, 
sum(c.avg_exam1) as c4, 
sum(c.avg_exam2) as c5,
count(c.gps_exam1) as c6, 
count(c.gps_exam2) as c7, 
count(c.avg_exam2) as c8, 
count(c.avg_exam2) as c9,
from report_sekolah a 
join report_stu b 
on a.uid=b.uid 
join report_exam c 
on b.uid=c.uid 
group by a.city

Вот запрос 2 :

 SELECT sum(b.stu_bil) as c10 
from   report_sekolah a 
join   report_stu b 
  on   a.uid=b.uid 
group by a.city

Мне нужно объединить это, чтобы получить фактический результат. То, что я сделал, — это с помощью объединения. Вот запрос :

 SELECT a.city as c1, 
       sum(c.gps_exam1) as c2, 
       sum(c.gps_exam2) as c3, 
       sum(c.avg_exam1) as c4, 
       sum(c.avg_exam2) as c5, 
       count(c.gps_exam1) as c6, 
       count(c.gps_exam2) as c7, 
       count(c.avg_exam2) as c8, 
       count(c.avg_exam2) as c9, 
       null as c10 
from   report_sekolah a 
join   report_stu b 
  on   a.uid=b.uid 
join   report_exam c 
  on   b.uid=c.uid 
group by a.city 
UNION ALL 
SELECT null as c1, 
       null as c2,  
       null as c3, 
       null as c4, 
       null as c5,  
       null as c6, 
       null as c7, 
       null as c8, 
       null as c9, 
       sum(b.stu_bil) as c10 
from   report_sekolah a 
join   report_stu b 
  on   a.uid=b.uid 
group by a.city

Но это дает неверный результат строки. затем я пытаюсь использовать запрос ниже, но неверный результат для общего числа студентов.

 SELECT a.city as c1, 
sum(c.gps_exam1) as c2, 
sum(c.gps_exam2) as c3, 
sum(c.avg_exam1) as c4, 
sum(c.avg_exam2) as c5,
count(c.gps_exam1) as c6, 
count(c.gps_exam2) as c7, 
count(c.avg_exam2) as c8, 
count(c.avg_exam2) as c9, 
sum(b.stu_bil) as c10
from report_sekolah a 
join report_stu b 
on a.uid=b.uid 
join report_exam c 
on b.uid=c.uid
group by a.city

Ответ №1:

Я должен изменить значение одного поля (c10), чтобы задать подзапрос.

 SELECT a.city as c1, 
sum(c.gps_exam1) as c2, 
sum(c.gps_exam2) as c3, 
sum(c.avg_exam1) as c4, 
sum(c.avg_exam2) as c5,
count(c.gps_exam1) as c6, 
count(c.gps_exam2) as c7, 
count(c.avg_exam2) as c8, 
count(c.avg_exam2) as c9, 
(SELECT sum(stu_bil) From report_stu Where a.uid=uid) as c10
from report_sekolah a 
join report_stu b 
on a.uid=b.uid 
join report_exam c 
on b.uid=c.uid
group by a.city

Комментарии:

1. первая строка верна. и следующая строка неверна. спасибо за ответ

Ответ №2:

 SELECT y.c1, y.c2, y.c3, y.c4, y.c5, y.c6, y.c7, y.c8, y.c9 , z.result
FROM
(
SELECT a.city as c1, 
sum(c.gps_exam1) as c2, 
sum(c.gps_exam2) as c3, 
sum(c.avg_exam1) as c4, 
sum(c.avg_exam2) as c5,
count(c.gps_exam1) as c6, 
count(c.gps_exam2) as c7, 
count(c.avg_exam2) as c8, 
count(c.avg_exam2) as c9,
    a.uid
from report_sekolah a 
join report_stu b 
on a.uid=b.uid 
join report_exam c 
on b.uid=c.uid GROUP BY a.city
) y
INNER JOIN
(
 SELECT sum(b.stu_bil) as result,
    b.uid
    from report_sekolah a join report_stu b on a.uid=b.uid group by a.city
) z
ON y.uid = z.uid