SQL 물어봣지? 안됀다고 해서 쌤플 구성해봣다

[재훈군(cjh94kr)]

DROP TABLE IF EXISTS `TEST1`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `TEST1` (

  `No` int(11) NOT NULL,

  `Name` varchar(45) DEFAULT NULL,

  `Point` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`No`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `TEST1`

--

LOCK TABLES `TEST1` WRITE;

/*!40000 ALTER TABLE `TEST1` DISABLE KEYS */;

INSERT INTO `TEST1` VALUES (1,'Science','2'),(2,'Medical','3'),(3,'Fuck','4'),(4,'Helpme','5'),(5,'SQL','3'),(6,'Suck','3'),(7,'Sun of beach','3'),(8,'7','3'),(9,'8','3'),(10,'9','2');

/*!40000 ALTER TABLE `TEST1` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `TEST2`

--

DROP TABLE IF EXISTS `TEST2`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `TEST2` (

  `Student` int(11) NOT NULL,

  `TNo` int(11) DEFAULT NULL,

  `Name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`Student`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `TEST2`

--

LOCK TABLES `TEST2` WRITE;

/*!40000 ALTER TABLE `TEST2` DISABLE KEYS */;

INSERT INTO `TEST2` VALUES (1,1,'aa'),(2,1,'bb'),(3,2,'aa'),(4,2,'bb'),(5,3,'aa'),(6,3,'aa'),(7,3,'bb'),(8,4,'aa'),(9,4,'cc');

/*!40000 ALTER TABLE `TEST2` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

----------------------------

SELECT a.`Name`, SUM(`Point`) FROM (select `Name`, `TNo` from TEST2 group by `Name`, `TNo`)a, TEST1 WHERE `No` = a.`TNo` group by a.`Name`

결과: