mysql - 返回零行，尽管 SQL 查询

Question

我正在尝试显示包含某些汽车详细信息的表格，即使它们不受欢迎。在我的例子中，例如，没有人喜欢 VW Golf，但我仍然想在如下表中显示详细信息:

carID   description     model   name        description     avgLikes
4       BMW Rules       VW      GERMANY     HatchBack       0.0  

这是我的数据库架构:

CREATE TABLE IF NOT EXISTS Users(
    userID INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    forename VARCHAR(50) NOT NULL,
    surname VARCHAR(50) NOT NULL,
    PRIMARY KEY (userID)
);

CREATE TABLE IF NOT EXISTS CarType(
    carTypeID INT NOT NULL AUTO_INCREMENT,
    description VARCHAR(80),
    PRIMARY KEY (carTypeID)
);  

CREATE TABLE IF NOT EXISTS Country(
    countryID INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (countryID)
);  

CREATE TABLE IF NOT EXISTS Cars(
    carID INT NOT NULL AUTO_INCREMENT,
    carTypeID INT NOT NULL,
    countryID INT NOT NULL,
    description VARCHAR(100) NOT NULL,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100),
    FOREIGN KEY (carTypeID) REFERENCES CarType(carTypeID),
    FOREIGN KEY (countryID) REFERENCES Country(countryID),
    PRIMARY KEY (carID)
);

CREATE TABLE IF NOT EXISTS Likes(
    userID INT NOT NULL,
    carID INT NOT NULL,
    likes DOUBLE NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (carID) REFERENCES Cars(carID)
);

CREATE TABLE IF NOT EXISTS Sold(
    userID INT NOT NULL,
    carID INT NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (carID) REFERENCES Cars(carID)
);

INSERT INTO Users VALUES 
(NULL, "micheal", "Micheal", "Sco"),
(NULL, "bensco", "Ben", "Sco"),
(NULL, "shanemill", "Shane", "Miller");

INSERT INTO CarType VALUES
(NULL, "Saloon"),
(NULL, "HatchBack"),
(NULL, "Low Rider");

INSERT INTO Country VALUES
(NULL, "UK"),
(NULL, "USA"),
(NULL, "JAPAN"),
(NULL, "GERMANY");

INSERT INTO Cars VALUES
(NULL, 1, 2, "Ford Mustang lovers", "Mustang", "Ford"),
(NULL, 2, 3, "Drift Kings", "Skyline", "Nissan"),
(NULL, 3, 1, "British classic", "Cooper", "Mini"),
(NULL, 2, 4, "BMW Rules", "Golf", "VW");

INSERT INTO Likes VALUES
(1, 1, 3),
(1, 2, 2),
(2, 2, 5),
(2, 1, 7),
(2, 1, 1),
(2, 1, 2);

INSERT INTO Sold VALUES
(1, 2),
(1, 3),
(1, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 3);

这是我的SQL 查询:

SELECT c.carID, c.description, c.model, cy.name, ct.description,
           l.avgLikes
    FROM Cars c INNER JOIN
         Country cy
         ON c.countryID = cy.countryID  AND cy.name = "Germany" INNER JOIN
         CarType ct
         ON c.carTypeID = ct.carTypeID LEFT JOIN
         (SELECT l.carId, AVG(Likes) as avgLikes
          FROM Likes l
          GROUP BY l.CarId
         ) l
         ON c.carID = l.carID 

我的 localhost phpmyadmin 服务器没有返回任何内容。基本上，因为没有存储关于这辆德国汽车的喜欢，所以没有计算和返回任何内容

非常感谢任何帮助

谢谢

Answer 1

您可以使用 COALESCE 平均点赞 - 换句话说，如果在您的平均查询中未找到任何结果，您将显示 0.00 而不是 NULL，如 COALESCE 将转换为第一个非空参数。

我在本地复制了您的数据库并使用所有国家/地区字符串进行了测试，并使用以下方法获得了预期的输出:

SELECT c.carID, c.description, c.model, cy.name, ct.description,
           COALESCE(l.avgLikes,'0.00') AS 'avglikes'
    FROM Cars c 
    INNER JOIN Country cy ON c.countryID = cy.countryID  AND cy.name = "Germany" 
    INNER JOIN CarType ct ON c.carTypeID = ct.carTypeID 
    LEFT JOIN
         (SELECT l.carId, AVG(Likes) as avgLikes
          FROM Likes l
          GROUP BY l.CarId
         ) l
         ON c.carID = l.carID 

简单但希望能为您提供成功的解决方案。