SQL Server 2005和C#中的查询 [英] query in sql server 2005 and 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
推荐答案
您应该使用交叉表查询,以帮助您获得预期的结果.这是最好的示例(针对学生成绩),可以为您提供帮助.
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屋!
查看全文