Closed. This question is off-topic。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗? Update the question,所以它是on-topic,用于堆栈溢出。
                        
                        5年前关闭。
                                                                                            
                
        
班上有一群学生,每个人都可以加入一个以上的俱乐部,并且不能参加。

我想检查哪些人不加入棒球俱乐部?

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

谢谢。

最佳答案

清楚地写出这样一个查询,即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
        )

关于mysql - 我想检查哪些人不加入棒球俱乐部? ,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28147182/

10-11 11:47