这里是sql新手，我想让这个表打印出所有拥有特定品牌/车型组合的人的工资、品牌和车型的总和。到目前为止，该表打印出所有汽车品牌和型号值，但sumwage列都为空。sumwage应该返回拥有make/model组合的人员的所有工资的总和（我的select语句一直在代码的底部）？敬请指教！
IF OBJECT_ID ('Person', 'U') IS NULL
BEGIN
CREATE TABLE Person (
ID INT NOT NULL PRIMARY KEY,
Name VARCHAR(32),
BirthDate DATETIME
);
INSERT INTO Person
( ID, Name, BirthDate )
VALUES
(311113, 'Dan Lu', '01-22-33'),
(123456, 'Seven Durant', '07-22-94'),
(100100, 'Choochootrain Lu', '12-17-56'),
(106542, 'Spider Ru', '07-22-36'),
(101010, 'Ru Ru', '04-30-84');
END
IF OBJECT_ID ('Job', 'U') IS NULL
BEGIN
CREATE TABLE Job (
ID INT NOT NULL PRIMARY KEY,
Company VARCHAR(64),
Wage FLOAT,
PersonID INT FOREIGN KEY REFERENCES Person(ID)
);
INSERT INTO Job
( ID, Company, Wage, PersonID )
VALUES
(01, 'Space Pizza Space', 1000.00, 311113),
(02, 'Bread', 46.44, 101010),
(03, 'Delivery Service', 400.99, 100100),
(04, 'Nike', 999900.01, 106542),
(05, 'Old McDonald', 6500210.77, 123456);
END
IF OBJECT_ID ('Car', 'U') IS NULL
BEGIN
CREATE TABLE Car (
ID INT NOT NULL PRIMARY KEY,
Make VARCHAR(32),
Model VARCHAR(32)
);
INSERT INTO Car
( ID, Make, Model )
VALUES
(1234, 'Lexus', 'SE'),
(4444, 'Tesla', 'X'),
(5007, 'Chevy', 'Cobalt'),
(7771, 'Ford', 'Runner'),
(6459, 'Toyota', 'Camry');
END
IF OBJECT_ID ('PersonCar', 'U') IS NULL
BEGIN
CREATE TABLE PersonCar (
ID INT,
PersonID INT FOREIGN KEY REFERENCES Person(ID),
CarID INT
);
INSERT INTO PersonCar
( ID, PersonID, CarID )
VALUES
(1, 311113, 1234),
(2, 123456, 4444),
(3, 100100, 5007),
(4, 106542, 7771),
(5, 101010, 6459);
END
SELECT SUM(Wage) AS SumWages, Car.Make, Car.Model
FROM Person
INNER JOIN PersonCar
ON PersonCar.ID = Person.ID
INNER JOIN Job
ON Job.PersonID = PersonCar.ID
Right JOIN Car
ON Car.ID = Person.ID
GROUP BY Wage, Make, Model;
2条答案
按热度按时间hs1ihplo1#
我认为你想要：
这将选择所有
Cars、 并遵循指向job表的关系，该表包含Wage每一个车主。数据按car模型和model进行聚合，并求出相应的工资。当给定的make/model元组没有车主时，求和结果为
null（如果只想从结果集中删除这些行，请使用INNER JOINs而不是LEFT JOINs） 是的。请注意，您不需要携带
Person表以获取此结果集。fslejnso2#
嘿，看起来你的连接有几个错误，看看是否像下面那样切换你的连接“开”，并将最后一个连接从右改为内。