任何人都可以帮助我在asp.net中找到一个SQL查询吗? [英] Any one please help me to find a sql query in asp.net?
本文介绍了任何人都可以帮助我在asp.net中找到一个SQL查询吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三个桌子.标记，学生和科目
标记
I have three tables. Marks, Students and Subjects
Marks
RowID StudentID SubjectID Mark
-------------------- ----------- ----------- -----------
1 1 1 50
2 1 2 60
3 1 3 70
4 1 4 100
5 1 5 45
6 1 6 75
7 2 1 55
8 2 2 75
9 2 3 65
10 2 4 100
11 2 5 88
12 2 6 95
13 3 1 40
14 3 2 32
15 3 3 20
16 3 4 33
17 3 5 50
18 3 6 47
19 4 1 100
20 4 2 95
21 4 3 70
22 4 4 65
23 4 5 85
24 4 6 73
25 5 1 55
26 5 2 65
27 5 3 75
28 5 4 85
29 5 5 57
30 5 6 67
31 6 1 75
32 6 2 80
33 6 3 90
34 6 4 55
35 6 5 54
36 6 6 53
37 7 1 44
38 7 2 65
39 7 3 66
40 7 4 77
41 7 5 88
42 7 6 52
43 8 1 30
44 8 2 70
45 8 3 75
46 8 4 45
47 8 5 33
48 8 6 66
49 9 1 55
50 9 2 66
51 9 3 77
52 9 4 88
53 9 5 65
54 9 6 79
学生
Students
StudentID RegNo Name Sex DOB Address
1 S1 Anderson Male 1996-01-01 00:00:00.000 72 Dewhurst Road, Cheshunt,Herts,EN8 9P
2 S2 Russell Male 1996-02-01 00:00:00.000 2 Wolsey Avenue,Cheshunt,Herts,EN7 5QB
3 S3 Phillip Male 1996-01-28 00:00:00.000 28 Second Avenue,Galley Hill,Waltham Abbey,Essex,EN9 2AW
4 S4 Paul Male 1996-03-02 00:00:00.000 30 Second Avenue,Galley Hill,Waltham Abbey,Essex,EN9 2AW
5 S5 Jaime Female 1996-04-04 00:00:00.000 10 Davies Street,Hertford,Herts,SG13 7BU
6 S6 Angela Female 1996-06-03 00:00:00.000 47 Cameron Drive,Waltham Cross,Herts,EN8
7 S7 Reena Female 1996-07-09 00:00:00.000 11 Bromleigh Close,Cheshunt,Herts,EN8 0RN
8 S8 Manu Male 1996-06-02 00:00:00.000 1 Blackthorn Close,Diss,Norfolk,IP22 4ZA
9 S9 Reeja Female 1996-07-02 00:00:00.000 4 Tregelles Road,Hoddesdon,Herts,EN11 9H
主题
Subjects
SubjectID Subject MaxTotal
1 English 100
2 Hindi 100
3 Malayalam 100
4 Physics 100
5 Chemistry 100
6 Maths 100
我想在新表中得到结果结果表中需要的列:主题，最高得分者(标记)，注册号
任何人都可以帮助我获得这一结果"
I want to get result in a new table Columns needed in the resultant table : Subject, Top Scorer (Mark), Reg.No
"Any one please help me to attain this result"
推荐答案
刷新与作业相关的问题不必包括给我解决方案"
据我了解，您希望每个学科的学生得分最高
1)我建议您首先将3个表连接在一起.
2)完成操作后，您将很快意识到必须进行很多行.因此，您必须过滤掉那些得分最高的学生.
3)找出如何在学科中获得最高分.
4)当您知道该怎么做时，您需要在1)中创建的sql的where子句中使用它，以仅使那些在每个学科中得分最高的学生
5)现在您应该有一个sql，可以在新表中为您提供所需的数据.并假设已经创建了表，则只需编写insert into mytable，然后编写已创建的sql，就可以插入该表中，只需选择必须插入表中的列即可.
我意识到其中有些可能很难理解，因为我不是最好的解释者，因此，如果您对此有更具体的问题，请留下评论，我会尽力澄清.
Refreshing to see a homework related question doesn''t have to include the "give me the solution"
As I understand it you want the students that has the highest mark in each subject
1) I suggest you start by joining the 3 tables together.
2) Once you''ve done that you''ll quickly realize that you have to many rows. So you''ll have to filter out those students that does not have the highest mark.
3) Find out how you get the highest mark in a subject.
4) When you know how to do that you need to use it in your where clause of the sql you made in 1) to only get those students with the highest mark in each subject
5) Now you should have a sql that gives you the data you want in a new table. and assuming your table is already created you can insert into that table just by writinginsert into mytableand then the sql you''ve created, just only selecting the columns which has to be inserted into the table.
I realize some of this might be difficult to understand, as I''m not the best to explain things, so if you have more specific questions about it leave a comment and I''ll try to clarify.
检查以下链接，其中包含有关如何通过示例实现此目的的详细说明
http://www.stevebin.net/Articles.aspx?ArtId=QL08 [ ^ ]
check the following link which has detailed explanation on how to acheive this with example
http://www.stevebin.net/Articles.aspx?ArtId=QL08[^]
这篇关于任何人都可以帮助我在asp.net中找到一个SQL查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
