資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化學
生物
數學
英語
經濟學
心理學
社會學
時尚研究
法律研究如何根據 MySQL 中的科目將學生分數顯示在單列中？
為此，請使用 UNION ALL。
我們首先建立一個表
mysql> create table DemoTable729 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), MySQLMarks int, CMarks int, JavaMarks int ); Query OK, 0 rows affected (0.40 sec)
使用 insert 命令在表中插入一些記錄
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Chris',94,67,75);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Robert',45,99,54);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('David',57,89,43);
Query OK, 1 row affected (0.12 sec)使用 select 語句從表中顯示所有記錄
mysql> select *from DemoTable729;
這將產生以下輸出 -
+-----------+-------------+------------+--------+-----------+ | StudentId | StudentName | MySQLMarks | CMarks | JavaMarks | +-----------+-------------+------------+--------+-----------+ | 1 | Chris | 94 | 67 | 75 | | 2 | Robert | 45 | 99 | 54 | | 3 | David | 57 | 89 | 43 | +-----------+-------------+------------+--------+-----------+ 3 rows in set (0.00 sec)
以下是對單列中顯示學生分數的查詢
mysql> select StudentId,StudentName,'MySQL' as SubjectName, MySQLMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'C' as SubjectName, CMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'Java' as SubjectName, JavaMarks as Score from DemoTable729;
這將產生以下輸出 -
+-----------+-------------+-------------+-------+ | StudentId | StudentName | SubjectName | Score | +-----------+-------------+-------------+-------+ | 1 | Chris | MySQL | 94 | | 2 | Robert | MySQL | 45 | | 3 | David | MySQL | 57 | | 1 | Chris | C | 67 | | 2 | Robert | C | 99 | | 3 | David | C | 89 | | 1 | Chris | Java | 75 | | 2 | Robert | Java | 54 | | 3 | David | Java | 43 | +-----------+-------------+-------------+-------+ 9 rows in set (0.00 sec)
更新於: 22-Aug-2019
2K+ 次瀏覽
廣告