解决方案
select R.Request_at as Day,convert(ifnull(G.num/count(*),0),decimal(3,2)) as 'Cancellation Rate'
from Trips as R
natural left outer join
(
select count(Status) as num,t.Request_at as Request_at
from Trips as t , Users as u,Users as s
where u.Banned='No' and s.Banned='No'and t.Status<>'completed'
and t.Client_Id = u.Users_Id and t.Driver_Id = s.Users_Id
and Request_at between convert('2013-10-01', date) and convert('2013-10-03', date)
group by Request_at
)as G,Users as P,Users as B
where P.Banned='No' and B.Banned='No'
and R.Client_Id = P.Users_Id and R.Driver_Id = B.Users_Id
and R.Request_at between convert('2013-10-01', date) and convert('2013-10-03', date)
group by R.Request_at
order by R.Request_at
网友评论