gpt4 book ai didi

mysql - 我需要帮助使用 count 进行查询

转载 作者:行者123 更新时间:2023-11-29 01:01:04 25 4
gpt4 key购买 nike

我需要选择一个列表,其中显示每个租车客户的客户 ID、头衔、名字和姓氏,并按客户姓氏的字母顺序排序,以及每个客户的预订数量。

我已经完成了第一部分,但不确定在哪里计算预订数量。

这是表格

create table customer
(customer_id char(4) primary key not null,
customer_sname varchar (30) not null,
customer_fname varchar (30) not null,
customer_title varchar (6) not null,
customer_address1 varchar (35) not null,
customer_address2 varchar (35) null,
customer_postcode varchar (25) null,
customer_phone varchar (30) null,
customer_email varchar (40) null,
customer_di varchar (40) not null)
ENGINE=InnoDB;

create table car_booking
(booking_id INTEGER AUTO_INCREMENT primary key not null,
car_id char (4) not null,
customer_id char (4) not null,
hire_sdate date not null,
hire_edate date not null)
engine=innodb

我做到了

select customer_id, customer_title, Customer_fname, customer_sname
from customer
where customer_id in
(select customer_id from car_booking )
order by customer_sname asc

谢谢

最佳答案

这将需要使用聚合函数 (COUNT)、GROUP BY 子句和 CAR_BOOKING 表的 LEFT JOIN:

   SELECT c.customer_id, c.customer_title, c.customer_fname, c.customer_sname,
COALESCE(COUNT(*), 0) AS num_bookings
FROM CUSTOMER c
LEFT JOIN CAR_BOOKING cb ON cb.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_title, c.customer_fname, c.customer_sname
ORDER BY c.customer_sname

因为有些列没有包含在像 COUNT 这样的聚合函数中,所以这些列需要在 GROUP BY 子句中定义。

我对 CAR_BOOKINGS 表使用了 LEFT OUTER JOIN 以返回没有任何预订的客户 - 这些记录将在 num_booking 列中显示为零值。您可以在查询中省略 LEFT 关键字以仅返回有预订的客户和计数。 COALESCE 是将空值转换为所需值的标准函数 - 在这种情况下,计数为空...

关于mysql - 我需要帮助使用 count 进行查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3173214/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com