postgre组

Question

我有一个这样的查询

select c.travelandsmile_id, c.name, c.surname
from customer c
where c.travelandsmile_id in
      (
        select s.travelandsmile_id
        from spent_kilometers s
        group by travelandsmile_id
        having count(s.kilometers)=1
      )

我想选择那些在表spent_kilometers中只显示一次且is大于30的记录。但是当我添加where s.kilometers > 30时，结果是错误的，并且根据第一次查询出现了更多的元组。

我该怎么做呢？

Answer 1

select travelandsmile_id, c.name, c.surname
from
    customer c
    inner join
    spent_kilometers s using (travelandsmile_id)
where s.kilometers > 30
group by travelandsmile_id, c.name, c.surname
having count(*) = 1

Answer 2

如果我没看错的话，您希望查找在spent_kilometers中有一条记录的所有客户，并且此记录必须具有约束s.kilometers > 30。

这可以使用下面的SQL来完成。

select c.travelandsmile_id, c.name, c.surname
from customer c
where c.travelandsmile_id in 
     ( /* find all customers that have only one record in spent_kilometers */
        select c.travelandsmile_id
        from spent_kilometers s
        group by travelandsmile_id having count(travelandsmile_id) = 1
    )
 and c.travelandsmile_id in 
     ( /* find all customers that have s.kilometers > 30 */
        select c.travelandsmile_id
        from spent_kilometers s
        where s.kilometers > 30
    );