总计嵌套选择

Question

我有两个表：一个是Tour和其他是ReservedTour。表中的列有：

Tour  | ReservedTour 
===========|============= 
id   | id 
city  | Tid 
capacity | number 
timeout | . 
.   | . 
.   | . 
.   | 
========== |====== 

我写的SQL语句，如

select *, (select sum(rt.`number`) from ReservedTour as rt where rt.`Tid`=t.id GROUP BY rt.`Tid`) as total 
    from Tour as t 
where City = 'alahom' and '1400-12-13' <= t.`timeout` and 4 < t.`Capacity`- total; 

但是这有一个错误 - 一共是不正确的。

我该如何解决这个问题？

Answer 1

试试这个：

select t.*, sum(rt.`number`) as total 
from Tour as t 
join ReservedTour as rt on rt.`Tid`=t.id 
where City = 'alahom' and t.`timeout`=> '1400-12-13' and t.`Capacity`-total > 4 
GROUP BY t.id 

Answer 2

我想你可能会寻找这样的事情：

SELECT T.*, RT.total 
FROM Tour T 
    JOIN (select Tid, sum(number) total 
      from ReservedTour 
      GROUP BY TId) RT ON T.Id = RT.TiD 
WHERE City = 'alahom' 
    AND '1400-12-13' <= t.`timeout` 
    AND 4 < t.Capacity-RT.total; 

Condensed SQL Fiddle Demo