SQL Server 2005和C#中的查询 [英] query in sql server 2005 and c#

问题描述

大家好！
我有问题，需要您的帮助.我想在DatagridView上显示所有学生的成绩，所以下面是我的问题.谁能帮我.感谢您的帮助.

Hello everybody!
I have a problem and need your help. I want to display all the mark of student on the DatagridView, so below is my problem. Could anyone help me please. I''ll appreciate your help.

create table STUDENT
(
   idstudent  char(10)primary key,
   .....info about student
)
//insert some data into table student
insert into STUDENT values(''123456789'',...)

create table SUBJECT
(
    idsubject  char(10),
    namesubject  char(10)
)
//i have two subject in this table such as (''111'', ''Math'') and (''112'', ''Physics'')

create table MARK
(
   idstudent  char(10) foreign key references STUDENT(idstudent),
   idsubject   char(10) foreign key references SUBJECT(idsubject),
   score   int
)
//i have two record like this (''123456789'', ''111'', 9) and (''123456789'', ''112'', 10)

因此，现在我想创建一个查询以在一行中显示一个学生的所有分数:

So now i want to create a query to display all the mark of one student on a row like this:

idstudent    idsubject1(Math)  idsubject2(Physics)
123456789       9                      10

推荐答案

您应该使用交叉表查询，以帮助您获得预期的结果.这是最好的示例(针对学生成绩)，可以为您提供帮助.

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx [

You should go with cross tab queries which help you to achieve the expected result. Here is best example (for student marks) which may help you.

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx[^]

Gud luck!!

我看到您使用当前数据库结构执行此操作的唯一方法是这样的:

The only way I can see you doing this with your current database structure is like this :

SELECT DISTINCT m.idstudent, (SELECT score FROM mark where idsubject = 111) AS idsubject1, (SELECT score FROM mark where idsubject = 112) AS idsubject2 FROM mark m;

，但这非常丑陋.我认为您应该重新考虑您的数据库结构，说实话.

希望对您有帮助

but that is pretty ugly. I think you should reconsider you database structure to be honest.

Hope this helps

这篇关于SQL Server 2005和C#中的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！