我想看看有哪些人不加入棒球俱乐部？

Question

班里有一群学生，每个人都可以参加一个以上的俱乐部，不能参加。

我想看看有哪些人不加入棒球俱乐部？

CREATE DATABASE `group` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `group`;

CREATE TABLE IF NOT EXISTS `club` (
  `ClubID` int(5) NOT NULL AUTO_INCREMENT,
  `ClubName` varchar(20) NOT NULL,
  PRIMARY KEY (`ClubID`)
);

INSERT INTO `club` (`ClubID`, `ClubName`) VALUES
(1, 'Baseball'),    (2, 'Basketball'),    (3, 'Tennis'),    (4, 'Snooker'),    (5, 'Football'),    (6, 'Swim'),    (7, 'Badminton'),    (8, 'Dance');

CREATE TABLE IF NOT EXISTS `member` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `ClubID` int(5) NOT NULL,
  `StudentID` int(5) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `member` (`id`, `ClubID`, `StudentID`) VALUES
(1, 1, 1),(2, 1, 5),(3, 1, 9),(4, 1, 18),(5, 2, 2),(6, 2, 4),(7, 2, 5),(8, 3, 3),(9, 3, 4),(10, 3, 6),(11, 3, 7),(12, 3, 8),(13, 3, 9),(14, 3, 10),(15, 3, 11),(16, 4, 11),(17, 4, 12),(18, 4, 13),(19, 4, 14),(20, 5, 2),(21, 5, 6),(22, 5, 8),(23, 5, 9),(24, 5, 10),(25, 5, 14),(26, 6, 1),(27, 6, 8),(28, 6, 14),(29, 7, 5),(30, 7, 6),(31, 7, 7),(32, 8, 11),(33, 8, 12);

CREATE TABLE IF NOT EXISTS `student` (
  `StudentID` int(5) NOT NULL AUTO_INCREMENT,
  `StudentName` varchar(20) NOT NULL,
  PRIMARY KEY (`StudentID`)
);

INSERT INTO `student` (`StudentID`, `StudentName`) VALUES
(1, 'Mary'),    (2, 'Paul'),    (3, 'Andy'),    (4, 'Tony'),    (5, 'Jack'),    (6, 'Sandy'),    (7, 'Nacy'),    (8, 'Cherry'),    (9, 'Cathy'),    (10, 'Jenny'),    (11, 'Kelly'),    (12, 'Ash'),    (13, 'Rose'),    (14, 'Bruce'),    (15, 'David'),    (16, 'Harry'),    (17, 'William'),    (18, 'Richard');

所有学生：https://drive.google.com/file/d/0B4PAmUA35G8FbFlxZGktamFsUkE/view

SELECT `Student`.StudentID, `Student`.StudentName, group_concat(distinct `Club`.`ClubName`) AS `ClubName`, group_concat(distinct `Club`.`ClubID`) AS ClubID FROM `Student` LEFT JOIN `Member` ON `Member`.`StudentID` = `Student`.`StudentID` LEFT JOIN `Club` ON `Member`.`ClubID` = `Club`.`ClubID` Group BY  `Student`.`StudentID`

棒球俱乐部(ClubID=1)学生：

SELECT `Student`.StudentID, `Student`.StudentName, group_concat(distinct `Club`.`ClubName`) AS `ClubName`, group_concat(distinct `Club`.`ClubID`) AS ClubID FROM `Student` LEFT JOIN `Member` ON `Member`.`StudentID` = `Student`.`StudentID` LEFT JOIN `Club` ON `Member`.`ClubID` = `Club`.`ClubID` WHERE `Club`.`ClubID`='1' Group BY  `Student`.`StudentID`

不是棒球俱乐部的学生？https://drive.google.com/file/d/0B4PAmUA35G8FRndRRmVWa3h4M1E/view?usp=sharing

谢谢。

Answer 1

编写类似这样的查询的清晰的写操作是not exists (比not in好，正如jarl注释的那样)：

select  *
from    Students s
where   not exists
        (
        select  *
        from    Member m
        join    Club c
        on      c.ClubID = m.ClubID
        where   ClubName = 'Baseball'
                and s.StudentID = m.StudentID
        )