编写一个查询来查找至少租过一部电影并且属于阿灵顿市的客户的全名

tpgth1q7  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(315)

我得到一个错误,说输出与给定的问题不匹配
sakila数据库: https://dev.mysql.com/doc/sakila/en/sakila-structure.html 下面是我的代码

with temp as (
select concat(b.first_name, ' ',b.last_name) as name,
count(a.rental_id >= 1) as rental_count
from rental a
inner join customer b ON   (a.customer_id=b.customer_id)
inner join address c ON    (b.address_id=c.address_id)
inner join city d ON       (c.city_id=d.city_id)
where d.city like "Arlington"
group by name
order by rental_count
)
select name from temp

预期结果是

full names of those customers who have rented at least one movie and belong to the city Arlington.

你能告诉我是什么错误吗?或者有别的办法吗?

drnojrws

drnojrws1#

你可以使用 having 子句,请尝试以下操作

select 
    concat(b.first_name, ' ',b.last_name) as name
from rental a
inner join customer b 
on a.customer_id = b.customer_id
inner join address c    
on b.address_id = c.address_id
inner join city d       
on c.city_id = d.city_id
where d.city = 'Arlington'
group by 
    concat(b.first_name, ' ',b.last_name)
having count(a.rental_id) >= 1
olqngx59

olqngx592#

select concat(first_name," ", last_name) as Customer_name
from rental
inner join customer
using (customer_id)
inner join address
using(address_id)
inner join city
using (city_id)
where city = "Arlington"
group by Customer_name
having count(rental_id) > 0

相关问题