数据库查询-数据集合的行转查询的列；一个表的所有数据作为查询的列

最新推荐文章于 2023-06-08 13:07:37 发布

菜鸟-编程

最新推荐文章于 2023-06-08 13:07:37 发布

阅读量323

点赞数

分类专栏： # MySQL 文章标签：数据库表数据集合的行转查询的列数据库一个表的所有数据作为查询的列

本文链接：https://blog.csdn.net/qq_36161345/article/details/94388155

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

以查询每个学生没门课程的成绩为例。

第一步：创建数据库。

-- 创建数据库，格式为 utf-8
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 切换到创建的数据库下
use test;
-- 学生表
CREATE TABLE student (
   id INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(30) NOT NULL ,
   sex VARCHAR(10) DEFAULT 'male'
);
-- 课程表
CREATE TABLE course(
   id INT PRIMARY KEY AUTO_INCREMENT,
   cname VARCHAR(30) NOT NULL
);
-- 成绩表
CREATE TABLE results(
   id INT PRIMARY KEY AUTO_INCREMENT,
   sid INT NOT NULL,
   cid INT NOT NULL,
   result INT NOT NULL,
   CONSTRAINT STUDENTID FOREIGN KEY(sid) REFERENCES student(id),
   CONSTRAINT COURSEID FOREIGN KEY(cid) REFERENCES course(id)
);

第二步：插入测试数据，使用存储过程。

CREATE DEFINER=`root`@`%` PROCEDURE `InsertResult`()
BEGIN
   -- 插入5000条学生的信息
   SET @num = 1;
   WHILE @num <= 5000 DO

       SET @studentname = '学生';

       INSERT INTO student (sname) VALUES (CONCAT(@studentname,@num)),
       (CONCAT(@studentname,@num + 1)),
       (CONCAT(@studentname,@num + 2)),
       (CONCAT(@studentname,@num + 3)),
       (CONCAT(@studentname,@num + 4)),
       (CONCAT(@studentname,@num + 5)),
       (CONCAT(@studentname,@num + 6)),
       (CONCAT(@studentname,@num + 7)),
       (CONCAT(@studentname,@num + 8)),
       (CONCAT(@studentname,@num + 9)),
       (CONCAT(@studentname,@num + 10)),
       (CONCAT(@studentname,@num + 11)),
       (CONCAT(@studentname,@num + 12)),
       (CONCAT(@studentname,@num + 13)),
       (CONCAT(@studentname,@num + 14)),
       (CONCAT(@studentname,@num + 15)),
       (CONCAT(@studentname,@num + 16)),
       (CONCAT(@studentname,@num + 17)),
       (CONCAT(@studentname,@num + 18)),
       (CONCAT(@studentname,@num + 19)),
       (CONCAT(@studentname,@num + 20));

       SET @num = @num + 20;
   END WHILE;

   -- 插入课程信息
   INSERT INTO course (cname) VALUES ('课程1'),('课程2'),('课程3'),('课程4'),('课程5');

   -- 为每个学生分别插入每门课程的成绩
   SET @num = 1;
   WHILE @num <= 5000 DO

       INSERT INTO results (sid,cid,result) VALUES (@num,1,91),(@num,2,92),(@num,3,93),(@num,4,94),(@num,5,95),
       (@num + 1,1,91),(@num + 1,2,92),(@num + 1,3,93),(@num + 1,4,94),(@num + 1,5,95),
       (@num + 2,1,91),(@num + 2,2,92),(@num + 2,3,93),(@num + 2,4,94),(@num + 2,5,95),
       (@num + 3,1,91),(@num + 3,2,92),(@num + 3,3,93),(@num + 3,4,94),(@num + 3,5,95),
       (@num + 4,1,91),(@num + 4,2,92),(@num + 4,3,93),(@num + 4,4,94),(@num + 4,5,95),
       (@num + 5,1,91),(@num + 5,2,92),(@num + 5,3,93),(@num + 5,4,94),(@num + 5,5,95),
       (@num + 6,1,91),(@num + 6,2,92),(@num + 6,3,93),(@num + 6,4,94),(@num + 6,5,95),
       (@num + 7,1,91),(@num + 7,2,92),(@num + 7,3,93),(@num + 7,4,94),(@num + 7,5,95),
       (@num + 8,1,91),(@num + 8,2,92),(@num + 8,3,93),(@num + 8,4,94),(@num + 8,5,95),
       (@num + 9,1,91),(@num + 9,2,92),(@num + 9,3,93),(@num + 9,4,94),(@num + 9,5,95);


       SET @num = @num + 10;
   END WHILE;
END

表数据：

学生表：

课程表：

成绩表：

第三步：编写查询的存储过程，将课程表中的课程转换成查询的列。

CREATE DEFINER=`root`@`%` PROCEDURE `FindResult`(IN `pageOffSet` varchar(256),IN `pageRows` varchar(256))
BEGIN
   -- 更改GROUP_CONCAT的长度
   SET GLOBAL group_concat_max_len=102400;
   SET SESSION group_concat_max_len=102400;

   -- 执行的sql
   SET @sql = NULL;

   -- 数据集合的行转查询的列
   SELECT
       GROUP_CONCAT(DISTINCT
           CONCAT(
               '(SELECT R.result FROM results AS R,course AS C,student AS S WHERE
               R.sid = S.id AND R.cid = C.id AND S.id = ST.id AND C.id = ',c.id,') AS \'',c.cname,'\''
           )
       ) INTO @sql
   FROM course c;

   -- 查询语句
   SET @sql = CONCAT('SELECT ST.sname ,', @sql,
' FROM student AS ST LIMIT ',pageOffSet,',',pageRows);

   -- 定义
   PREPARE result FROM @sql;
   -- 执行预处理
   EXECUTE result;
   -- 删除
   DEALLOCATE PREPARE result;

END

结果：