mysql - 使用mySQL创建预订汇总-连接多个表，计算多个字段

Question

我正在尝试创建一个表格来显示漂流业务的预订摘要。 EER 图显示了一切是如何相关的。我正在为如何每次旅行只显示 1 行而苦苦挣扎(我返回多行并且数字成倍增加)。到目前为止，这是我的代码。

use www;

 SELECT 
    d.destination_name,
    tt.trip_type_name,
    t.trip_number,
    t.trip_date,
    (e.first_name + e.last_name) AS guide_name,
    t.trip_capacity,
    COUNT(r.guest_id) AS guests_booked,
    (COUNT(r.guest_id)-t.trip_capacity) AS positions_available
FROM
    employees e,
    destination d,
    trip_type tt,
    trips t,
    reservation r
GROUP BY d.destination_name , tt.trip_type_name , t.trip_number , t.trip_date , guide_name , t.trip_capacity
ORDER BY d.destination_name , tt.trip_type_name , t.trip_date , t.trip_number;

好的，所以我已经弄清楚了我现在正在努力连接 GUIDE NAME 的 JOIN，它显示为数字 0(零)。我对另一个表有同样的问题，该表需要显示来自与 ID 和旅行编号相对应的串联名字和姓氏的客人姓名。

这是我的代码:

use www;
SELECT 
d.destination_name,
tt.trip_type_name,
t.trip_number,
t.trip_date,
t.trip_capacity,
CONCAT(e.nick_name+' '+e.last_name AS guide_name
COUNT(r.guest_id) AS guests_booked,
(t.trip_capacity - COUNT(r.guest_id)) AS positions_available
FROM
trip_type tt
    JOIN
trips t ON tt.trip_type_code = t.trip_type_code
    JOIN
destination d ON t.destination_code = d.destination_code
    JOIN
reservation r ON t.trip_number = r.trip_number
GROUP BY trip_number;

Second SQL query 

use www;
SELECT 
d.destination_name,
tt.trip_type_name,
t.trip_number,
t.trip_date,
CONCAT(e.last_name + ', ' + e.first_name) AS guide_name,
CONCAT(g.last_name + ', ' + g.first_name) AS guest_name,
ex.exp_name AS guest_experience,
g.age AS guest_age,
g.weight AS guest_weight,
g.swimmer AS guest_is_swimmer,
g.mobile_phone AS guest_mobile_phone
FROM
trip_type tt
    JOIN
trips t ON tt.trip_type_code = t.trip_type_code
    JOIN
destination d ON t.destination_code = d.destination_code
    JOIN
reservation r ON t.trip_number = r.trip_number
    JOIN
guests g ON r.guest_id = g.guest_id
    JOIN
experience ex ON ex.exp_code = g.exp_code
    JOIN
employees e ON t.guide_employee_id = e.employee_id
ORDER BY d.destination_name , tt.trip_type_name , t.trip_date , g.last_name , e.employee_id

Answer 1

您需要关系条件，否则您将获得表之间的笛卡尔积。

SELECT 
    d.destination_name,
    tt.trip_type_name,
    t.trip_number,
    t.trip_date,
    (e.first_name + e.last_name) AS guide_name,
    t.trip_capacity,
    COUNT(r.guest_id) AS guests_booked,
    (COUNT(r.guest_id)-t.trip_capacity) AS positions_available
FROM trips t
inner join  employees e on e.employee_id = t.employee_employee_id
inner join  destination d on d.destination_code = t.destination_code
inner join  trip_type tt  on tt.trip_type_cde = t.trip_type_cde
inner join   reservation r on r.trip_number = t.reservation_trip_number 
GROUP BY d.destination_name , tt.trip_type_name , t.trip_number , t.trip_date , guide_name , t.trip_capacity
ORDER BY d.destination_name , tt.trip_type_name , t.trip_date , t.trip_number;