需要帮助来解决SQL查询。

本文介绍了需要帮助来解决SQL查询。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在学生表中，数据以此形式出现，

in the student table , the data is present in this form,

sID sName sub marks 1 Savita EC1 30 1 Savita EC2 19 1 Savita EC3 28 1 Savita EC4 30 1 Savita EC5 60 2 Vidya EC1 90 2 Vidya EC2 80 2 Vidya EC3 85 2 Vidya EC4 75 2 Vidya EC5 99 3 Tanesh EC1 75 3 Tanesh EC2 80 3 Tanesh EC3 85 3 Tanesh EC4 28 3 Tanesh EC5 86

i希望使用sql查询获取以下表格中的记录。

i wants to fetch the records in the below form using an sql query.

Sname EC1 EC2 EC3 EC4 EC5 Total Savita 30 19 28 30 60 167 Tanesh 75 80 85 28 86 354 Vidya 90 80 85 75 99 429

i使用SQL查询：

i used that SQL Query:

select fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5]) from (select fstudentname ,FSubject ,Fmarks from Student) a PIVOT(sum(Fmarks) For fsubject in (EC1,EC2,EC3,EC4,EC5) )b

查询工作正常但我想要使用PIVOT我想使用JOIN。这是可能的吗？ 我尝试了什么： SQL我使用的查询是获取每条记录。但问题是我想要一个SQL查询而不使用像PIVOT等所有函数。是否可以使用JOIN获取此表单中的记录？请帮帮我。 ：）

the query works fine but i dn want to use PIVOT i wants to use JOIN. IS that POSSIBLE? What I have tried: the SQL query i used is this to fetch the each records. but the problem is that i want a SQL query without using any functions like PIVOT and all. Is that possible to get the records in this form using JOIN?? Please help me out. :)

推荐答案

PIVOT是正确的方法，它正是它的设计目的。 你可以用它做一个JOIN - 或者更确切地说是一些JOIN - 但它会很麻烦，长篇大论，效率低下并且需要维持PITA。 保持PIVOT：它的工作原理，这是正确的选择。 PIVOT is the right method to do this, it is exactly what it is designed for. You could do it with a JOIN - or rather with quite a few JOINs - but it would be cumbersome, long winded, less efficient and a PITA to maintain. Keep the PIVOT: it works, and it's the right thing to use.

首先，请阅读我的评论[EIDT]和OriginalGriff的回答[/编辑]。 这是一个想法，如何在不使用枢轴的情况下实现这一目标。 CASE WHEN ... END 解决方案： First of all, please, read my comment [EIDT] and OriginalGriff's answer[/EDIT]. Here is an idea, how to achieve that without using pivot. CASE WHEN ... END solution: SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5]) FROM ( SELECT fStudentname, EC1 = SUM(CASE WHEN FSubject = 'EC1' THEN Fmarks ELSE NULL), EC2 = SUM(CASE WHEN FSubject = 'EC2' THEN Fmarks ELSE NULL), EC3 = SUM(CASE WHEN FSubject = 'EC3' THEN Fmarks ELSE NULL), EC4 = SUM(CASE WHEN FSubject = 'EC4' THEN Fmarks ELSE NULL), EC5 = SUM(CASE WHEN FSubject = 'EC5' THEN Fmarks ELSE NULL) FROM Student GROUP BY fStudentname ) AS Src

加入解决方案：

JOIN's solution:

DECLARE @student TABLE(stuID INT, fStudentname VARCHAR(30), FSubject VARCHAR(30), Fmarks INT) INSERT INTO @student (stuID, fStudentname, FSubject, Fmarks) VALUES(1, 'Savita', 'EC1', 30), (1, 'Savita', 'EC2', 19), (1, 'Savita', 'EC3', 28), (1, 'Savita', 'EC4', 30), (1, 'Savita', 'EC5', 60), (2, 'Vidya', 'EC1', 90), (2, 'Vidya', 'EC2', 80), (2, 'Vidya', 'EC3', 85), (2, 'Vidya', 'EC4', 75), (2, 'Vidya', 'EC5', 99), (3, 'Tanesh', 'EC1', 75), (3, 'Tanesh', 'EC2', 80), (3, 'Tanesh', 'EC3', 85), (3, 'Tanesh', 'EC4', 28), (3, 'Tanesh', 'EC5', 86) SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5]) FROM ( SELECT A.fStudentname, A.EC1, B.EC2, C.EC3, D.EC4, E.EC5 FROM ( (SELECT fStudentname, SUM(Fmarks) AS EC1 FROM @student WHERE FSubject = 'EC1' GROUP BY fStudentname) AS A INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC2 FROM @student WHERE FSubject = 'EC2' GROUP BY fStudentname) AS B ON A.fStudentname = B.fStudentname INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC3 FROM @student WHERE FSubject = 'EC3' GROUP BY fStudentname) AS C ON A.fStudentname = C.fStudentname INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC4 FROM @student WHERE FSubject = 'EC4' GROUP BY fStudentname) AS D ON A.fStudentname = D.fStudentname INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC5 FROM @student WHERE FSubject = 'EC5' GROUP BY fStudentname) AS E ON A.fStudentname = E.fStudentname ) ) AS Final

注：已测试临时表。您必须使用实际表名替换 @student 。