在C＃中的mysql中打印所有主题的学生标记表 [英] Print student marksheet in all subjects in mysql in C#

问题描述

嗨
我无法在一张桌子上显示学生的成绩

表数据如下：

Hi I have trouble displaying students' grades in one table
The table data is as follows:

CREATE TABLE `tb_marks` (
  `id` int(11) NOT NULL,
  `id_subject` int(11) NOT NULL,
  `code_student` int(11) NOT NULL,
  `round` varchar(50) NOT NULL,
  `mark` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
------------------
INSERT INTO `tb_marks` (`id`, `id_subject`, `code_student`, `round`, `mark`) VALUES
(3, 1, 2, 'first', '45'),
(2, 1, 3, 'first', '45'),
(4, 2, 2, 'first', '46'),
(5, 3, 2, 'first', '0'),
(6, 4, 2, 'first', '84'),
(7, 5, 2, 'first', '77'),
(8, 6, 2, 'first', '65');
---------------------------------
CREATE TABLE `tb_student_old` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `school` varchar(50) NOT NULL,
  `code` int(11) NOT NULL,
  `age` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-------------------
INSERT INTO `tb_student_old` (`id`, `name`, `school`, `code`, `age`) VALUES
(1, 'ahmad', 'school', 0, NULL),
(2, 'noor', 'school', 0, NULL),
(3, 'salim', 'school', 0, NULL),
(4, 'emad', 'school', 0, NULL);
------------------------------------------
CREATE TABLE `tb_subject` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
------------------
INSERT INTO `tb_subject` (`id`, `name`) VALUES
(1, 'islamic'),
(2, 'arabic'),
(3, 'english'),
(4, 'math'),
(5, 'science'),
(6, 'social'),
(7, 'geographically'),
(8, 'date'),
(9, 'national');

我需要查询或编程，如下表所示：

I need the query or programming that gives me the table as follows:

SNo Student Name   islamic|arabic|english|math|science|social|geographic|Total
      1    ahmad		88	88	88	88   88	    88	    88	      8888

我尝试过：

What I have tried:

SELECT `tb_marks`.`id` , `tb_marks`.`round`, `tb_marks`.`mark` , `tb_subject`.`name` , `tb_student_old`.`name`, `tb_student_old`.`school` FROM `tb_marks` INNER JOIN `tb_subject` ON `tb_subject`.`id` = `id_subject` INNER JOIN `tb_student_old` ON `tb_student_old`.`id` = `code_student` WHERE code_student='2';
But the result was not right:

推荐答案

Aah，数据透视表。



对于MySql，您可以使用 Group_Concat（）指令来执行此操作。以下文章将此功能与其他RDBMS版本一起演示

http：// www .artfulsoftware.com / infotree / qrytip.php？id = 78 [ ^ ]

Aah, the Pivot Table.

For MySql, you would use the Group_Concat() instruction to do this. The following article demos this functionality along with other RDBMS versions
http://www.artfulsoftware.com/infotree/qrytip.php?id=78[^]

您需要一个SELECT语句;请参阅 MySQL :: MySQL 8.0参考手册:: 13.2.10 SELECT语法 [ ^ ]

You need a SELECT statement; see MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Syntax[^]

这篇关于在C＃中的mysql中打印所有主题的学生标记表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！