mysql - 评估一列，使其等于某个值而不等于其他值？

Question

表格:

CREATE TABLE Participation(
ParticipationId INT UNSIGNED AUTO_INCREMENT, 
SwimmerId       INT UNSIGNED NOT NULL,
EventId         INT UNSIGNED NOT NULL,
Committed       BOOLEAN,
CommitTime      DATETIME,
Participated    BOOLEAN,
Result          VARCHAR(100),
Comment         VARCHAR(100),
CommentCoachId  INT UNSIGNED,
CONSTRAINT participation_pk PRIMARY KEY(ParticipationId),
CONSTRAINT participation_ck_1 UNIQUE(SwimmerId, EventId),
CONSTRAINT participation_swimmer_fk FOREIGN KEY(SwimmerId) 
    REFERENCES Swimmer(SwimmerId),
CONSTRAINT participation_event_fk FOREIGN KEY(EventId) 
    REFERENCES Event(EventId),
CONSTRAINT participation_coach_fk FOREIGN KEY(CommentCoachId) 
    REFERENCES Coach(CoachId)
);

CREATE TABLE Swimmer(
SwimmerId       INT UNSIGNED AUTO_INCREMENT, 
LName           VARCHAR(30) NOT NULL,
FName           VARCHAR(30) NOT NULL,
Phone           VARCHAR(12) NOT NULL,
EMail           VARCHAR(60) NOT NULL,
JoinTime        DATE NOT NULL,
CurrentLevelId  INT UNSIGNED NOT NULL,
Main_CT_Id      INT UNSIGNED NOT NULL,
Main_CT_Since   DATE NOT NULL,
CONSTRAINT swimmer_pk PRIMARY KEY(SwimmerId),
CONSTRAINT swimmer_level_fk FOREIGN KEY(CurrentLevelId) 
    REFERENCES Level(LevelId),
CONSTRAINT swimmer_caretaker_fk FOREIGN KEY(Main_CT_Id) 
    REFERENCES Caretaker(CT_Id)
);

CREATE TABLE Event(
EventId      INT UNSIGNED AUTO_INCREMENT, 
Title        VARCHAR(100) NOT NULL,
StartTime    TIME NOT NULL,
EndTime      TIME NOT NULL,
MeetId       INT UNSIGNED NOT NULL,
LevelId      INT UNSIGNED NOT NULL,
CONSTRAINT event_pk PRIMARY KEY(EventId),
CONSTRAINT event_meet_fk FOREIGN KEY(MeetId) 
    REFERENCES Meet(MeetId),
CONSTRAINT event_level_fk FOREIGN KEY(LevelId) 
    REFERENCES Level(LevelId)
);

有 3 个表格，我想列出参加过赛事 3 但未参加过赛事 4 的游泳运动员的姓名。

首先我尝试了这个:SELECT s.FName AS "fname", s.LName AS "lname"FROM Swimmer s, Participation p, Event e WHERE s.SwimmerId = p.SwimmerId AND e.EventId = p .EventId AND p.EventId = 3 AND p.EventId != 4;

我在网上搜索了一段时间，但一直没能找到这样的东西。

这是我最接近找到解决方案的方法:Where clause for equals a field, not equal another但我不确定我的“wp_posts.id”和“wp_term_relationships”在我的情况下会是什么。

Answer 1

你可以试试这个，伙计:

SELECT s.FName AS 'fname', s.LName AS 'lname'
FROM swimmer s
    INNER JOIN participation p ON p.SwimmerId = s.SwimmerId
    -- swimmer(s) that did not participate in event 4
    INNER JOIN participation p4  ON p4.SwimmerID = s.SwimmerId
        AND p4.EventId != 4
WHERE p.EventId = 3;