sql语句在同一个tabe中获取deffrent类型的数量？ [英] sql statement get count of deffrent types in same tabe ?

问题描述

你好

i有一个表名学生

和课程和分数

i需要获得学生姓名如下所示和课程名称每个课程获得

的平均值



i需要sql语句来获得结果

hello
i have a table name Students
and Courses and marks
i need to get the students name as show below and the courses name and get the avarage of
each course

i need sql statment to get the result as

student Name    |  Math     | English | art
Mike            |  90       | 50      | 88
ali             |  99       | 88      | 69

推荐答案

你应该查询这样的东西

You should query something like this

SELECT 'StudentName' AS Name,
[Math], [Art], [English]
FROM
(SELECT Course.Course_name as Course, Marks
    FROM Marks JOIN Course ON Marks.CourseId = Course.CourseId JOIN Student on Marks.StudentID= Student.ID) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

如果所有列都来自同一个表，这是一个查询..

Here is a query if all columns are from same table..

SELECT Name,
[Math], [Art], [English]
FROM
(SELECT Name,Course, Marks
    FROM mTab ) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

我测试过一个CTE（带有一些值的临时表）在下面自己尝试..

I tested with a CTE (temp table with some values) try it yourself below..

;With mTab as
(
SELECT'Guru' AS Name, 'Math' AS Course, '100' AS Marks
UNION
SELECT'Guru' AS Name, 'Art' AS Course, 90 AS Marks  UNION
SELECT'Guru' AS Name, 'English' AS Course, 80 AS Marks  UNION
SELECT'Prasad' AS Name, 'Math' AS Course, 70 AS Marks  UNION
SELECT'Prasad' AS Name, 'Art' AS Course, 60 AS Marks  UNION
SELECT'Prasad' AS Name, 'English' AS Course, 65 AS Marks  UNION
SELECT'Mike' AS Name, 'Math' AS Course, 55 AS Marks  UNION
SELECT'Mike' AS Name, 'Art' AS Course, 50 AS Marks  UNION
SELECT'Mike' AS Name, 'English' AS Course, 45 AS Marks  UNION
SELECT'Ali' AS Name, 'Math' AS Course, 40 AS Marks  UNION
SELECT'Ali' AS Name, 'Art' AS Course, 35 AS Marks  UNION
SELECT'Ali' AS Name, 'English' AS Course, 20 AS Marks
)
SELECT Name,
[Math], [Art], [English]
FROM
(SELECT Name,Course, Marks
    FROM mTab ) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

您的输出将如下所示

your Output will be something like below

Name	Math	        Art		English
Ali	40		35		20
Guru	100		90		80
Mike	55		50		45
Prasad	70		60		65

如果你想得到你所描述的结果，你需要使用 PIVOT [ ^ ]。



其他资源：

SQL SERVER中的Pivot和Unpivot表 [ ^ ]

在SQL查询中使用Pivot的简单方法 [ ^ ]

If you want to get result as you described, you need to use PIVOT[^].

Other resources:
Pivot and Unpivot table in SQL SERVER[^]
Simple Way To Use Pivot In SQL Query[^]

这篇关于sql语句在同一个tabe中获取deffrent类型的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！