如何在sql server中删除查询中的错误并使用sql查询的概念 [英] how to remove error in query and use concept of sql query in sql server

问题描述

我有一种情况

一张表是 STUDENT_CRpt 列

i have one situation
one table is STUDENT_CRpt columns

row_id(identity),
Stu_Id(int),
Stu_Name(varchar),
Class(varchar),
Subject(varchar),
Marks(int)

< br $>


第二个表是 STUDENT_DTL_Crpt 列：

second table is STUDENT_DTL_Crpt columns:

Stu_Id(int),
Class(varchar),
Gender(varchar),
Address(varchar)

现在进入 STUDENT_CRpt 表6行（原始数据）

Now in STUDENT_CRpt table 6 rows (raw data)

1   raam    V     Maths      56
2   Sham    V     Maths      59
3   Sham    V     English    49
4   raam    V     English    56
1   geeta   VI    English    60
1   geeta   VI    science    60

根据这个英文最高分为56分，数学分类为59分和Vi分类为60分/秒


所以我试着跟随查询得到输出像

According to this in english highest marks are 56 and in maths 59 of V class and of vi class 60

so i tried following query to get output like

1   raam    V   Maths      56   59
2   Sham    V   Maths      59   59
3   Sham    V   English    49   56
4   raam    V   English    56   56
1   geeta   VI   English   60   60
1   geeta   VI   science   60   60

此处每行的最后一栏（以粗体显示）是各自各科目的最高分< br $>


i希望你得到我的情景........

所以我用下面的查询

here last column of each row (which is bold) is "highest marks in respective subject of respective class

i hope u got my scenario........
so i used below query

select s.Stu_Name,s.Class,s.Marks,s.Subject ,d.Gender,d.Address,
(select MAX(s.Marks) as ''Highest Marks'' from STUDENT_CRpt  group by s.Subject,s.Class)


 from STUDENT_CRpt s,STUDENT_DTL_Crpt d where s.Stu_ID=d.Stu_ID and s.Class=d.Class group by s.Stu_Name,s.Class,d.Address,d.Gender,s.Subject,s.Marks order by Class

i也需要告知最高分，并且每一行都有相应的课程和科目







但是收到错误



每个GROUP BY表达式必须包含至少一个不是外部引用的列。



i做错了什么..告诉我这样我可以退出这个



问候，

谢谢

i need to tell highest marks too with respective class and subject in each row



but getting an error

Each GROUP BY expression must contain at least one column that is not an outer reference.

i did somewhere wrong ..do tell so that i can get out of this

regards,
thanks

推荐答案

试试这个：

Try this:

SELECT s.Stu_ID, s.Stu_Name, s.Class, MAX(s.Marks) AS 'Highest marks', s.Subject, d.Gender, d.Address
FROM STUDENT_CRpt AS s INNER JOIN  STUDENT_DTL_Crpt AS d ON s.Stu_ID=d.Stu_ID and s.Class=d.Class
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject, d.Gender,d.Address 
ORDER BY s.Class, s.Stu_Name

谢谢你的解释;）





在课堂上获得最高分的主题：

Thank you for explanation ;)


To get higest marks in class for subject:

SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
FROM STUDENT_CRpt AS s
GROUP BY s.Class, s.Subject

为课堂上每位学生获得最高分：

To get highest marks for each student in class for subject:

SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
FROM STUDENT_CRpt AS s
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject

为每个学生获得科目和课程的最高分：

To get highest marks for each student for subject and for class:

SELECT t1.*, t2.[Highest marks] AS 'Highest in Class'
FROM (
        --get all students with their marks
	SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
	FROM STUDENT_CRpt AS s
	GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject
	) AS t1 LEFT JOIN (
                --get highest marks in class
		SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
		FROM STUDENT_CRpt AS s
		GROUP BY s.Class, s.Subject
		) AS t2 ON t1.Class = t2.Class AND t1.Subject = t2.Subject

注意：未经测试，因为此时我无法访问SQL Server。

我希望它有所帮助。

[/ EDIT]

Note: Not tested, because i haven''t access to SQL Server at this moment.
I hope it help.
[/EDIT]

这篇关于如何在sql server中删除查询中的错误并使用sql查询的概念的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！