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

tpgth1q7 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(260)

我得到一个错误，说输出与给定的问题不匹配
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.

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

sql mysql inner-join

来源：https://stackoverflow.com/questions/62368904/write-a-query-to-find-the-full-names-of-those-customers-who-have-rented-at-least

2条答案

按热度按时间

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

赞(0）回复(0）举报 2021-08-09

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

赞(0）回复(0）举报 2021-08-09

我来回答

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

2条答案

相关问题

热门标签

最新问答