SQL - объединение 2 производных таблиц

Question

У меня есть 2 запроса, каждый из которых возвращает довольно сложные таблицы, полученные из данных в куче других таблиц, и я хотел бы объединить их вместе, а затем использовать group by в таблице суперсоединений.

Я не думаю, что смогу получить эту таблицу с помощью одного запроса, потому что каждый из них обращается к разному набору таблиц, и история сортировки заключается в том, что невозможно объединить всю информацию, требуемую обоими, в одну строку (по крайней мере, Я не могу придумать способ)

Первый запрос:

select
    pr.runName,
    cp.firstname,
    mp.name
from
    passrun as pr,
    passrunpoly as prp,
    mappolygon as mp,
    cmnemployee as ce,
    cmnperson as cp,
    passschedule as ps

where 
    pr.runid = prp.runid
    and prp.polyid = mp.polyid
    and pr.employeeid = ce.employeeid
    and ce.personid = cp.personid
    and pr.scheduleid = ps.scheduleid
    and ps.ldate = 20170403

Второй запрос:

select 
    mp.name,
    count(distinct pbl.lat) as Stops,
    count(case when pba.spacetype = 'S' then pb.ldate end) / 2 as S,
    count(case when pba.spacetype = 'WC' then pb.ldate end) / 2 as WC,
    count(case when pba.spacetype = 'WK' then pb.ldate end) / 2 as WK
from 
    passbookingactivity as pba,
    passbooking as pb,
    passbookingleg as pbl,
    mappolygon as mp
where 
    pb.bookingid = pba.bookingid
    and pb.bookingid = pbl.bookingid
    and mp.polyid = pbl.addresspolygonid
    and pb.ldate = 20170403
    and pb.servicetypeid = 5
group by mp.name, mp.abbreviation

Я хочу присоединиться к ним по mp.name, а затем группировать по pr.runname, cp.firstname, mp.name

Answer 1

Вы можете использовать обычные табличные выражения (я также изменил ваш синтаксис соединения):

with table_one as (
select
    pr.runName,
    cp.firstname,
    mp.name
from
    passrun as pr
    inner join passrunpoly as prp on pr.runid = prp.runid
    inner join mappolygon as mp on prp.polyid = mp.polyid
    inner join cmnemployee as ce on pr.employeeid = ce.employeeid
    inner join cmnperson as cp on ce.personid = cp.personid
    inner join passschedule as ps on pr.scheduleid = ps.scheduleid
where 
    ps.ldate = 20170403

), table_two as (

select 
    mp.name,
    count(distinct pbl.lat) as Stops,
    count(case when pba.spacetype = 'S' then pb.ldate end) / 2 as S,
    count(case when pba.spacetype = 'WC' then pb.ldate end) / 2 as WC,
    count(case when pba.spacetype = 'WK' then pb.ldate end) / 2 as WK
from 
    passbookingactivity as pba
    passbooking as pb on pb.bookingid = pba.bookingid
    passbookingleg as pbl on pb.bookingid = pbl.bookingid
    mappolygon as mp on mp.polyid = pbl.addresspolygonid
where 
    pb.ldate = 20170403
    and pb.servicetypeid = 5
group by mp.name, mp.abbreviation
)

select
    one.runname,
    one.name,
    one.firstname
from table_one one
    inner join table_two two on one.name = two.name
group by one.runname,one.name,one.firstname

Answer 2

Хотя я не делал это слишком красивым, это должно сработать. Дай мне знать, как это происходит!

select
a.runname
,a.firstname
, a.name
from 
(select
    pr.runName,
    cp.firstname,
    mp.name
from
    passrun as pr,
    passrunpoly as prp,
    mappolygon as mp,
    cmnemployee as ce,
    cmnperson as cp,
    passschedule as ps

where 
    pr.runid = prp.runid
    and prp.polyid = mp.polyid
    and pr.employeeid = ce.employeeid
    and ce.personid = cp.personid
    and pr.scheduleid = ps.scheduleid
    and ps.ldate = 20170403)a
inner join (
select 
    mp.name,
    count(distinct pbl.lat) as Stops,
    count(case when pba.spacetype = 'S' then pb.ldate end) / 2 as S,
    count(case when pba.spacetype = 'WC' then pb.ldate end) / 2 as WC,
    count(case when pba.spacetype = 'WK' then pb.ldate end) / 2 as WK
from 
    passbookingactivity as pba,
    passbooking as pb,
    passbookingleg as pbl,
    mappolygon as mp
where 
    pb.bookingid = pba.bookingid
    and pb.bookingid = pbl.bookingid
    and mp.polyid = pbl.addresspolygonid
    and pb.ldate = 20170403
    and pb.servicetypeid = 5
group by mp.name, mp.abbreviation)b
 on a.name = b.name
group by a.runname,a.firstname, a.name