总的SQL查询和计数从多个表(SQL query of Sum and Count from mul

我有以下两个表：

1. blist

• BookingID
• AdultNo
• ChildNo
• 预定日期

2. BHandle

• BookingID
• TicketingStatus
• FinalSellingPrice
• FinalNett
• 员工

我想要做的就是在distinct Staff用Sum of (SellingPrice) Sum of (NettPrice) Profit (Sum of sellingPrice)- Sum of (NettPrice)) ，没有和平的是(AdultNo + ChildNo)和另算在BookingID作为无担保的

WHERE BookingDate >= fromDate AND BookingDate <= toDate 
    AND TicketingStatus='CP'

一些看起来像这样的（占总数的数字在底部并不重要，因为我将它们写入csv格式，我会处理总有），但我需要弄清楚如何先得到查询。

这是查询我可以从第二表获得BHandle

SELECT Staff, SUM(FinalSellingPrice) AS gross, SUM(FinalNett) AS cost
FROM BHandle
WHERE ticketingstatus ='CP'
GROUP BY Staff

这是我第一次表BList查询

SELECT (adultno+childno) AS pax 
fFROM om BList
WHERE bookingdate >='01-mar-2013 00:00'
AND bookingdate <= '15-may-2013 23:59'

我怎样才能结合这2个查询在一起吗？

Answer 1:

事情是这样的（假设所有列都是非空）：

select Staff,
    sum(FinalSellingPrice) as gross,
    sum(FinalNett) as cost,
    sum(FinalSellingPrice - FinalNett) as profit,
    sum(AdultNo+ChildNo) as pax,
    count(1) as bookings
from Blist b
inner join BHandle bh on b.BookingID = bh.BookingID
where b.BookingDate >= fromDate
    and b.BookingDate <= toDate
    and bh.TicketingStatus = 'CP'
group by staff;

Answer 2:

要做到这一点的方法之一是使用union all与聚合：

select staff, sum(gross) as gross, sum(cost) as cost, sum(pax) as pax,
       sum(numbookings) as numbookings
from ((SELECT Staff, SUM(FinalSellingPrice) AS gross, SUM(FinalNett) AS cost,
              null as pax, null as numbookings
       FROM BHandle
       WHERE ticketingstatus ='CP'
       GROUP BY Staff
      ) union all
      (select staff, null as gross, null as cost, (adultno+childno) AS pax ,
              count(*) as numbookings
       from blist join
            bhandle
            on blist.bookingid = bhandle.bookingid
       group by staff
      )
     ) t
group by staff

文章来源: SQL query of Sum and Count from multiple tables