时间戳

首页 > 解决方案 > 当我运行此查询时，为什么其他参与者获得特定于其他参与者的值？

mysql - 当我运行此查询时，为什么其他参与者获得特定于其他参与者的值？

问题描述

DROP DATABASE IF EXISTS ACTOR;
CREATE DATABASE ACTOR;
USE ACTOR;

CREATE TABLE IF NOT EXISTS actor(
    actorID INT PRIMARY KEY,
    birth_name VARCHAR(50) NOT NULL,
    age INT,
    reside VARCHAR(45) NOT NULL,
    partner VARCHAR(45)
    );

CREATE TABLE IF NOT EXISTS movie(
    movieID INT PRIMARY KEY,
    movie_name VARCHAR(50) NOT NULL,
    year_made INT
);

CREATE TABLE IF NOT EXISTS tag(
    tagID INT PRIMARY KEY,
    tag_list VARCHAR(50) NOT NULL,
    actorID INT REFERENCES actor(actorID)
);

CREATE TABLE IF NOT EXISTS rating(
    ratingID INT PRIMARY KEY,
    actorID INT REFERENCES actor(actorID),
    movieID INT REFERENCES movie(movieID),
    actor_rating DECIMAL(3,1) NOT NULL,
    movie_rating DECIMAL(3,1) NOT NULL
);


INSERT INTO actor VALUES
    (1,'Benjamin Géza Affleck-Boldt',47,'US','Kelleth Cuthbert'),
    (2,'Caleb Casey Affleck',44,'US','Floriana Lima'),
    (3,'Kevin Norwood Bacon',61,'US','Kyra Sedgwick'),
    (4,'Alexander Rae Baldwin III',61,'US','Hilaria Baldwin'),
    (5,'Jason Kent Bateman',50,'US','Amanda Anka');

INSERT INTO movie VALUES
    (11,'Armageddon',1998),
    (12,'American Pie',1999),
    (13,'Apollo 13',1995),
    (14,'The Departed',2006),
    (15,'Hancock',2008)
;

INSERT INTO tag VALUES
    (21,'actor, american, director',1),
    (22,'actor, american, talented',2),
    (23,'actor, american, musician',3),
    (24,'actor, american, funny',4),
    (25,'actor, american, Cute',5)
;

INSERT INTO rating VALUES
    (51,1,11,6.2, 6.2),
    (52,2,12,6.7, 6.1),
    (53,3,13,7.1, 7.0),
    (54,4,14,6.6, 8.0),
    (55,5,15,7.2, 5.9)
    ;


SELECT birth_name, actor_rating
FROM actor, rating
WHERE age IN(61)
;

如果他们的年龄是 61，我试图返回出生姓名和演员等级

查询结果

Kevin Norwood Bacon     6.2
Alexander Rae Baldwin   6.2
Kevin Norwood Bacon     6.7
Alexander Rae Baldwin   6.7
Kevin Norwood Bacon     7.1
Alexander Rae Baldwin   7.1
Kevin Norwood Bacon     6.6
Alexander Rae Baldwin   6.6
Kevin Norwood Bacon     7.2
Alexander Rae Baldwin   7.2

我将如何解决这个问题，以免每个演员都获得与其他演员有关的价值？我查看了我的屏幕，试图为这样一个简单的查询剖析问题，但我仍然不知道是什么原因造成的。可能是我的代码错误，还是我的查询？我正在上初学者课程，刚开始使用简单的查询，所以坚持下去将不胜感激，谢谢。

标签： mysqlsqlmysql-workbench

解决方案

试试这个查询：

SELECT birth_name, actor_rating
FROM actor, rating
WHERE age IN(61) and rating.actorID = actor.actorID

推荐阅读