需要帮助来解决SQL查询。 [英] Help is needed to solve the SQL query.

问题描述

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

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查询获取以下表格中的记录。< br $>

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 。

这篇关于需要帮助来解决SQL查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！