MySQL查询通过联接4个不同的表来创建数据透视表 [英] MySQL query to create a pivot table by joining 4 different table
本文介绍了MySQL查询通过联接4个不同的表来创建数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有 4 个 mySQL 表，分别是 Exams、Marks、Student 和 Subject.下面提供了带有数据的Sql.我需要类似以下屏幕截图的输出.
分数是由针对特定科目和学生对的所有分数加在一起的所有分数及其正确的权重百分比计算得出的(分数权重为0的考试将被忽略)
我尝试使用以下查询，但结果没有给出累积分数，因此需要帮助.
SET @sql = NULL;选择GROUP_CONCAT(DISTINCT)CONCAT('MAX(IF(sj.SubjectId = ''', SubjectId,''', pa.Marks, NULL)) AS ',SubjectId)) 进入@sql来自主题；SET @sql = CONCAT('SELECT s.ID, s.StudentID, s.FirstName, s.LastName, ', @sql, '来自学生的JOIN 标记为 paON pa.StudentID = s.StudentID AND pa.OrganizationId = s.OrganizationId参加考试开启p.ExamId = pa.ExamId和p.OrganizationId = pa.OrganizationId加入主题 sj开启p.SubjectId = sj.SubjectId和pa.OrganizationId = sj.OrganizationId在哪里p.Weightage>0GROUP BY s.ID')；从@sql 准备 stmt;执行stmt;请帮助. FIDDLE LINK
创建表`考试`(`ID` int(6)NOT NULL，`ExamId` varchar(20)收集utf8_unicode_ci NOT NULL，`Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL，日期datetime(6)默认为NULL，`TotalMarks` int(6) 非空，SubjectId varchar(30)收集utf8_unicode_ci NOT NULL，`Weightage` int(6)NOT NULL默认值为'0'，`OrganizationId` int(6) 非空)ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;---转储表Exams的数据--插入``检查''(ID，``ExamId''，``名称''，``日期''，``TotalMarks''，``SubjectId''，``重量''，``OrganizationId'')值(8, 'EX_0001', '测试 1', '2020-05-30 17:15:38.000000', 50, 'SUB_0002', 0, 116),(9，'EX_0002'，'测试2'，'2020-05-17 17:15:19.000000'，30，'SUB_0001'，0，116)，(10, 'EX_0003', '测试 3', '2020-05-17 17:15:51.000000', 30, 'SUB_0003', 10, 116),(11，'EX_0004'，'Test 45'，'2020-05-19 15:15:08.000000'，30，'SUB_0001'，0，116)，(12，"EX_0005"，期末考试"，"2020-05-20 15:30:53.000000"，100，"SUB_0001"，80、116)，(13, 'EX_0006', 'Terminal 3', '2020-05-20 15:30:03.000000', 50, 'SUB_0001', 10, 116);-------------------------------------------------------------- 表`Marks`的表结构--创建表`Marks`(`ID` int(11) 非空，`StudentId` varchar(30) COLLATE utf8_unicode_ci NOT NULL，`ExamId` varchar(30)收集utf8_unicode_ci NOT NULL，`Marks` int(6) 非空，`OrganizationId` int(6)非空) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;---- 转储表`Marks`的数据--INSERT INTO `Marks`(`ID`、`StudentId`、`ExamId`、`Marks`、`OrganizationId`)值(14, 'S_100000001', 'EX_0004', 30, 116),(15, 'S_100000001', 'EX_0003', 25, 116),(16，'S_100000001'，'EX_0002'，77，116)，(17, 'S_100000003', 'EX_0003', 15, 116),(18, 'S_100000003', 'EX_0004', 12, 116),(19，'S_100000003'，'EX_0001'，12，116)，(20, 'S_100000002', 'EX_0004', 20, 116),(21，'S_100000002'，'EX_0003'，21，116)，(22, 'S_100000001', 'EX_0005', 80, 116),(23，'S_100000002'，'EX_0005'，90，116);------------------------------------------------------------表`Student`的表结构--创建表`学生`(`ID` int(6)NOT NULL，`GradeId` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL，学生ID varchar(30)收集utf8_unicode_ci默认为空，`OrganizationId` int(6) DEFAULT NULL,`FirstName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL，LastName varchar(30)字符集utf8 COLLATE utf8_unicode_ci默认为NULL，`FatherFirstName` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL，`FatherLastName` varchar(30)CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL，`DateOfBirth` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL，PlaceOfBirth varchar(30)收集utf8_unicode_ci默认为空，Sex varchar(30)收集utf8_unicode_ci默认值为NULL，Carnet varchar(30)收集utf8_unicode_ci默认为空，`MobilePhone` bigint(8) DEFAULT NULL,`地址` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL，MotherFirstName varchar(30)CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL，`MotherLastName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL，`FatherMobilePhone` bigint(8)默认为NULL，`MotherMobilePhone` bigint(8) DEFAULT NULL,`FatherProfession` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL，`MotherProfession` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL，`Observations` varchar(30)收集utf8_unicode_ci默认为NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;---- 转储表 `Student` 的数据--将学生插入(学生ID(`ID`，`GradeId`，`StudentID`，`OrganizationId`，`FirstName`，`LastName`，`FatherFirstName`，`FatherLastName`，`DateOfBirth`，`PlaceOfBirth`，`Sex`，`Carnet`、`MobilePhone`、`Address`、`MotherFirstName`、`MotherLastName`、`FatherMobilePhone`、`MotherMobilePhone`、`FatherProfession`、`MotherProfession`、`Observations`)值(21, 'G_016', 'S_100000001', 116, '学生', '一', '', '', '', '', '男', NULL, 8178109047, '', '', '',0，0，NULL，NULL，NULL)，(22，'G_016'，'S_100000002'，116，'Student'，'two'，''，''，''，'，'female'，NULL，0，''，''，''，0，0，NULL，NULL，NULL)，(23, 'G_002', 'S_100000003', 116, 'Student3', '三', NULL, NULL, NULL, NULL, '男', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL， 空值);------------------------------------------------------------表`Subject`的表结构--创建表`主题`(`ID` int(6)NOT NULL，SubjectId varchar(20)收集utf8_unicode_ci NOT NULL，`Name` varchar(30)COLLUT utf8_unicode_ci默认为NULL，`缩写` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,`GradeId` varchar(10) COLLATE utf8_unicode_ci NOT NULL，`OrganizationId` int(6)NOT NULL，`StaffId` varchar(30)收集utf8_unicode_ci NOT NULL默认'0')ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;---- 转储表主题"的数据--INSERT INTO `Subject`(`ID`、`SubjectId`、`Name`、`Abbreviation`、`GradeId`、`OrganizationId`、`StaffId`)值(12，"SUB_0001"，英语1A"，英语_1A"，"G_016"，116，"E_100000030")，(13，"SUB_0002"，英语1B"，英语_1B"，"G_002"，116，"0")，(14, 'SUB_0003', '科学 1A', 'Sci_1A', 'G_016', 116, 'E_100000030'); 解决方案
好的，你的最后一条评论，你的查询看起来像这样
<块引用> SET @sql = NULL;选择GROUP_CONCAT(DISTINCTCONCAT('SUM(IF(sj.SubjectId = ''', SubjectId,''', ROUND((pa.`Marks` * p.`Weightage`/100),1),0)) AS ',SubjectId)) 进入@sql从主题;SET @sql = CONCAT('SELECT s.ID，s.StudentID，s.FirstName，s.LastName，'，@sql，'来自学生的JOIN 标记为 paON pa.StudentID = s.StudentID AND pa.OrganizationId = s.OrganizationId参加考试p在 p.ExamId = pa.ExamId AND p.OrganizationId = pa.OrganizationId加入主题sjON p.SubjectId = sj.SubjectId AND pa.OrganizationId = sj.OrganizationId在哪里p.Weightage>0按 s.ID 分组ORDER BY s.ID');#SELECT @sql;从@sql 准备 stmt;执行stmt;身份证 |学生证 |名字 |姓氏 |SUB_0001 |SUB_0002 |SUB_0003-: |:---------- |:-------- |:------- |-------:|-------:|-------:21 |S_100000001 |学生 |一|71.7 |0.0 |2.522 |S_100000002 |学生|二|72.0 |0.0 |2.123 |S_100000003 |学生3 |三 |0.0 |1.2 |1.5
db<>fiddle 这里
I have 4 mySQL tables namely Exams, Marks, Student and Subject. Sql with data is provided below. I need the output like below screenshot.
Marks is calculated from all Marks added together for the particular subject and student pair with their proper Weightage in Percentage (Exams with 0 Weightage are ignored)
I tried using the below query but the result didn't gave the cumulative marks so need help.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(sj.SubjectId = ''', SubjectId,''', pa.Marks, NULL)) AS ',SubjectId)
) INTO @sql
FROM Subject;
SET @sql = CONCAT('SELECT s.ID, s.StudentID, s.FirstName, s.LastName, ', @sql, '
FROM Student s
JOIN Marks AS pa
ON pa.StudentID = s.StudentID AND pa.OrganizationId = s.OrganizationId
JOIN Exams p
ON p.ExamId = pa.ExamId AND p.OrganizationId = pa.OrganizationId
JOIN Subject sj
ON p.SubjectId = sj.SubjectId AND pa.OrganizationId = sj.OrganizationId
WHERE p.Weightage > 0
GROUP BY s.ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Please help. FIDDLE LINK
CREATE TABLE `Exams` (
`ID` int(6) NOT NULL,
`ExamId` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`Date` datetime(6) DEFAULT NULL,
`TotalMarks` int(6) NOT NULL,
`SubjectId` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`Weightage` int(6) NOT NULL DEFAULT '0',
`OrganizationId` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `Exams`
--
INSERT INTO `Exams` (`ID`, `ExamId`, `Name`, `Date`, `TotalMarks`, `SubjectId`, `Weightage`, `OrganizationId`) VALUES
(8, 'EX_0001', 'Test 1', '2020-05-30 17:15:38.000000', 50, 'SUB_0002', 0, 116),
(9, 'EX_0002', 'Test 2', '2020-05-17 17:15:19.000000', 30, 'SUB_0001', 0, 116),
(10, 'EX_0003', 'Test 3', '2020-05-17 17:15:51.000000', 30, 'SUB_0003', 10, 116),
(11, 'EX_0004', 'Test 45', '2020-05-19 15:15:08.000000', 30, 'SUB_0001', 0, 116),
(12, 'EX_0005', 'Final Exam', '2020-05-20 15:30:53.000000', 100, 'SUB_0001', 80, 116),
(13, 'EX_0006', 'Terminal 3', '2020-05-20 15:30:03.000000', 50, 'SUB_0001', 10, 116);
-- --------------------------------------------------------
--
-- Table structure for table `Marks`
--
CREATE TABLE `Marks` (
`ID` int(11) NOT NULL,
`StudentId` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`ExamId` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`Marks` int(6) NOT NULL,
`OrganizationId` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `Marks`
--
INSERT INTO `Marks` (`ID`, `StudentId`, `ExamId`, `Marks`, `OrganizationId`) VALUES
(14, 'S_100000001', 'EX_0004', 30, 116),
(15, 'S_100000001', 'EX_0003', 25, 116),
(16, 'S_100000001', 'EX_0002', 77, 116),
(17, 'S_100000003', 'EX_0003', 15, 116),
(18, 'S_100000003', 'EX_0004', 12, 116),
(19, 'S_100000003', 'EX_0001', 12, 116),
(20, 'S_100000002', 'EX_0004', 20, 116),
(21, 'S_100000002', 'EX_0003', 21, 116),
(22, 'S_100000001', 'EX_0005', 80, 116),
(23, 'S_100000002', 'EX_0005', 90, 116);
-- --------------------------------------------------------
--
-- Table structure for table `Student`
--
CREATE TABLE `Student` (
`ID` int(6) NOT NULL,
`GradeId` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`StudentID` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrganizationId` int(6) DEFAULT NULL,
`FirstName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`LastName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`FatherFirstName` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`FatherLastName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`DateOfBirth` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`PlaceOfBirth` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Sex` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Carnet` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`MobilePhone` bigint(8) DEFAULT NULL,
`Address` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`MotherFirstName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`MotherLastName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`FatherMobilePhone` bigint(8) DEFAULT NULL,
`MotherMobilePhone` bigint(8) DEFAULT NULL,
`FatherProfession` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`MotherProfession` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Observations` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `Student`
--
INSERT INTO `Student` (`ID`, `GradeId`, `StudentID`, `OrganizationId`, `FirstName`, `LastName`, `FatherFirstName`, `FatherLastName`, `DateOfBirth`, `PlaceOfBirth`, `Sex`, `Carnet`, `MobilePhone`, `Address`, `MotherFirstName`, `MotherLastName`, `FatherMobilePhone`, `MotherMobilePhone`, `FatherProfession`, `MotherProfession`, `Observations`) VALUES
(21, 'G_016', 'S_100000001', 116, 'Student', 'One', '', '', '', '', 'male', NULL, 8178109047, '', '', '', 0, 0, NULL, NULL, NULL),
(22, 'G_016', 'S_100000002', 116, 'Student', 'two', '', '', '', '', 'female', NULL, 0, '', '', '', 0, 0, NULL, NULL, NULL),
(23, 'G_002', 'S_100000003', 116, 'Student3', 'three', NULL, NULL, NULL, NULL, 'male', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `Subject`
--
CREATE TABLE `Subject` (
`ID` int(6) NOT NULL,
`SubjectId` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`Name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Abbreviation` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`GradeId` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`OrganizationId` int(6) NOT NULL,
`StaffId` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `Subject`
--
INSERT INTO `Subject` (`ID`, `SubjectId`, `Name`, `Abbreviation`, `GradeId`, `OrganizationId`, `StaffId`) VALUES
(12, 'SUB_0001', 'English 1A', 'Eng_1A', 'G_016', 116, 'E_100000030'),
(13, 'SUB_0002', 'English 1B', 'Eng_1B', 'G_002', 116, '0'),
(14, 'SUB_0003', 'Science 1A', 'Sci_1A', 'G_016', 116, 'E_100000030');
解决方案
Ok with your last comment, your query would look like this
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(sj.SubjectId = ''', SubjectId,''', ROUND((pa.`Marks` * p.`Weightage` / 100),1),0)) AS ',SubjectId) ) INTO @sql FROM Subject; SET @sql = CONCAT('SELECT s.ID, s.StudentID, s.FirstName, s.LastName, ', @sql, ' FROM Student s JOIN Marks AS pa ON pa.StudentID = s.StudentID AND pa.OrganizationId = s.OrganizationId JOIN Exams p ON p.ExamId = pa.ExamId AND p.OrganizationId = pa.OrganizationId JOIN Subject sj ON p.SubjectId = sj.SubjectId AND pa.OrganizationId = sj.OrganizationId WHERE p.Weightage > 0 GROUP BY s.ID ORDER BY s.ID'); #SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt;
ID | StudentID | FirstName | LastName | SUB_0001 | SUB_0002 | SUB_0003 -: | :---------- | :-------- | :------- | -------: | -------: | -------: 21 | S_100000001 | Student | One | 71.7 | 0.0 | 2.5 22 | S_100000002 | Student | two | 72.0 | 0.0 | 2.1 23 | S_100000003 | Student3 | three | 0.0 | 1.2 | 1.5
db<>fiddle here
这篇关于MySQL查询通过联接4个不同的表来创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
