我在使用MySQL时遇到了问题。我希望根据行来创建动态列。以下是详细信息:
SELECT `marks`.`id` , `marks`.`studentID` , `marks`.`subjectID` , `marks`.`mark`
FROM `Mark` `marks`
LEFT OUTER JOIN `Student` `students` ON ( `students`.`id` = `marks`.`studentID` )
WHERE (
`students`.`classID` =1
)
LIMIT 0 , 30
My Output is
+----+-----------+-----------+------+
| id | studentID | subjectID | mark |
+----+-----------+-----------+------+
| 1 | 1 | 1 | 20 |
| 2 | 1 | 2 | 36 |
| 3 | 2 | 1 | 47 |
| 4 | 2 | 2 | 43 |
+----+-----------+-----------+------+
4 rows in set (0.00 sec)
Output I need is
+----+-----------+-----------+-----------+
| id | studentID | subject_1 | subject_2 |
+----+-----------+-----------+-----------+
| 1 | 1 | 20 | 36 |
| 2 | 2 | 47 | 43 |
+----+-----------+-----------+-----------+
4 rows in set (0.00 sec)
科目数量可以根据“科目”表中的条目而变化。我只需要每个用户显示所有成绩的一行。以下是我使用的表结构。
--
-- Table structure for table `Mark`
--
CREATE TABLE IF NOT EXISTS `Mark` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentID` int(11) NOT NULL,
`subjectID` int(11) NOT NULL,
`mark` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
--
-- Table structure for table `Student`
--
CREATE TABLE IF NOT EXISTS `Student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`classID` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
--
-- Table structure for table `Subject`
--
CREATE TABLE IF NOT EXISTS `Subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
感谢您的提前感激。