连带的问题。sql语句

Question

select h_id,work_no,(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=0) import,(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=1) noshow,(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=2) cancel from orderlist group by h_id,work_no,guest_to_hotel_status order by achive desc

Answer 1

select t.*,b.work_no ,achive,achive+import-noshow-cancel as total from @users b right join 
(
select datepart(m,updatetime) month,c.hotel_id as h_id,
(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=0) import,
(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=1) noshow,
(select count(guest_to_hotel_status) from orderlist where guest_to_hotel_status=2) cancel
from orderlist c
group by datepart(m,updatetime),hotel_id)t
on b.id=t.h_id