MYSQL 未显示所有插入的值

Question

我应该能够在一个 SQL SELECT 语句中显示客户姓名、宠物名称、程序、成本和总成本。我只得到一行值，其中一个值为 NULL。我无法弄清楚我的 SQL 语句或表有什么问题导致它不返回所有输入的值。这是我得到的结果的图像:

CREATE DATABASE IF NOT EXISTS vet;
USE vet;

CREATE TABLE IF NOT EXISTS customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(70)
    );

CREATE TABLE IF NOT EXISTS invoice (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_date DATE,
    customer_id INT ,
    CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS pet (
    pet_id INT PRIMARY KEY AUTO_INCREMENT,
    pet_name VARCHAR(50),
    customer_id INT,
    CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS procedures (
    procedure_id INT PRIMARY KEY AUTO_INCREMENT,
    procedure_name VARCHAR(70),
    amount DECIMAL
);

CREATE TABLE IF NOT EXISTS invoice_pet (
    invoice_id INT,
    pet_id INT,
    CONSTRAINT FOREIGN KEY (invoice_id) REFERENCES invoice(invoice_id),
    CONSTRAINT FOREIGN KEY (pet_id) REFERENCES pet(pet_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS procedures_pet (
    procedure_id INT,
    pet_id INT,
    CONSTRAINT FOREIGN KEY (procedure_id) REFERENCES procedures(procedure_id),
    CONSTRAINT FOREIGN KEY (pet_id) REFERENCES pet(pet_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


INSERT INTO invoice (invoice_date) VALUES ('2004-04-05');
INSERT INTO invoice (invoice_date) VALUES ('2014-12-05');
INSERT INTO invoice (invoice_date) VALUES ('2009-08-29');
INSERT INTO invoice (invoice_date) VALUES ('2016-07-15');

INSERT INTO customer (customer_name) VALUES ('John Garett');
INSERT INTO customer (customer_name) VALUES ('Mary Wist');
INSERT INTO customer (customer_name) VALUES ('Beth Smith');
INSERT INTO customer (customer_name) VALUES ('Rick Sanchez');

INSERT INTO pet (pet_name, customer_id) VALUES ('Rover', 1);
INSERT INTO pet (pet_name, customer_id) VALUES ('Max', 3);
INSERT INTO pet (pet_name, customer_id) VALUES ('Munchie', 4);
INSERT INTO pet (pet_name, customer_id) VALUES ('Dixon', 2);
INSERT INTO pet (pet_name, customer_id) VALUES ('Lucky', 4);

INSERT INTO procedures (procedure_name, amount) VALUES ('Rabies Vaccination', 30.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Sterilization', 190.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Dental Surgery', 120.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Cystotomy', 200.00);

INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (1, 1);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (2, 1);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (1, 4);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (3, 2);

INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (1, 1);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (3, 2);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (4, 4);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (2, 1);


SELECT inv.invoice_id, cust.customer_name, p.pet_name, pro.procedure_name, 
pro.amount AS cost, SUM(amount) AS totalcost
FROM vet.procedures pro LEFT JOIN vet.procedures_pet propet
ON pro.procedure_id = propet.procedure_id
LEFT JOIN vet.pet p
ON propet.pet_id = p.pet_id
LEFT JOIN vet.invoice_pet invpet
ON p.pet_id = invpet.pet_id
LEFT JOIN vet.invoice inv
ON invpet.invoice_id = inv.invoice_id
LEFT JOIN vet.customer cust
ON inv.customer_id = cust.customer_id

Answer 1

下面列出了每位顾客、他们的宠物、这些宠物接受过的手术(如果有)以及这些手术的费用。

您没有说出“总成本”的含义(每个客户的总成本？每个宠物？总计？)，所以我选择了每个客户的成本(请参阅相关的子查询)

SELECT    c.customer_name,
          p.pet_name,
          pr.procedure_name,
          pr.amount,
          (
            SELECT  SUM(amount)
            FROM    procedures pr1
                    JOIN procedures_pet pp1
                      ON pr1.procedure_id = pp1.procedure_id
                    JOIN pet p1
                      ON p1.pet_id = pp1.pet_id
                    JOIN customer c1
                      ON p1.customer_id = c1.customer_id
            WHERE c1.customer_id = c.customer_id
          ) AS totalcost
FROM      customer c
          JOIN pet p
              ON c.customer_id = p.customer_id
          LEFT JOIN procedures_pet pp
              ON pp.pet_id = p.pet_id
          LEFT JOIN procedures pr
              ON pr.procedure_id = pp.procedure_id
ORDER BY customer_name,
         pet_name,
         procedure_name;