SQL Server 2005和C#中的查询

本文介绍了SQL Server 2005和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

推荐答案

您应该使用交叉表查询，以帮助您获得预期的结果.这是最好的示例(针对学生成绩)，可以为您提供帮助. www.sql-programmers/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. www.sql-programmers/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