如何获得此表的查询 [英] how get this query for this table
本文介绍了如何获得此表的查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这张桌子怎么样
how have this table
coursid coursename teachername stage startdate
1 x pro1 1 2010/10/20
1 x pro3 2 2010/11/30
2 y proz 1 2009/11/10
2 y proy 2 2010/1/10
3 z prow 1 2010/3/10
3 z pro4 2 2010/4/50
4 d pro5 1 2010/8/10
5 f pro6 1 2010/9/10
i想要这个表查询sql
i want this table with query to sql
cousrid=1 teachername stage startdate
coursename=x pro1 1 2010/10/20
pro3 2 2010/11/30
--------------------------------------------------------------
courseid=2 proz 1 2009/11/10
coursename=y proy 2 2010/1/10
------------------------------------------------------------
courseid=3 prow 1 2009/11/10
coursename=z pro4 2 2010/1/10
----------------------------------------------------------
courseid=4 pro5 1 2010/8/10
coursename=d
---------------------------------------------------------------
courseid=5 pro6 1 2010/9/10
coursename=f
---------------------------------------------------------------
推荐答案
您唯一要做的就是将数据分组到courseid和coursename。您可以从MSDN学习GROUP BY， http://msdn.microsoft.com/en-us/library /ms177673.aspx [ ^ ]
第二种方法（但我不确定它是否会起作用）是DISTINCT子句，它将选择不同的值记录。请仔细阅读，看看它是否符合您的需求。 http://msdn.microsoft.com/en-us/library/ms132161.aspx [ ^ ]
第一次出价将是SQL的GROUP BY。
旁注：但是，如果我必须编写这些表并自己进行此应用程序开发。我个人想分开那些东西。我会创建两个表，以最小化对象的重复。在您的表中，而不是重复courseid和coursename，创建一个新表并保存CourseID和CourseName，并在此表中使用CourseID。它会更好，你的数据也会得到管理。
The only thing that you're doing is, that you're grouping the data on to the courseid and coursename. You can learn the GROUP BY from MSDN, http://msdn.microsoft.com/en-us/library/ms177673.aspx[^]
Second method (but I am not sure whether it will work in this or not) is the DISTINCT clause, that will select the distinct value records. Do give it a read, and see if it fits your needs. http://msdn.microsoft.com/en-us/library/ms132161.aspx[^]
The first bid would be the GROUP BY thing for SQL.
Side note: However, if I had to write these tables and do this application development myself. I would personally want to seperate those things. I would have created two tables, to minimize the repetition of the objects. In your table instead of repeating the courseid and coursename, create a new table and save the CourseID and CourseName there, and just use CourseID in this table. It will be better, and your data will be managed too.
如解决方案1所述，你的桌面设计不是很好。
你应该看一下如何规范化您的数据库并重新设计结构。
从长远来看，它将使您的生活更轻松。
数据库规范化 [ ^ ]
3正常表格数据库教程 [ ^ ]
数据库规范化基础知识 [ ^ ]
如何使用ASP.Net中的GridView。
使用DataSource控件访问数据 [ ^ ]
As mentioned in solution 1, your table design is not very good.
You should look into how to normalize your database and redesign the structure.
It will make your life a lot easier in the long run.
Database normalization[^]
3 Normal Forms Database Tutorial[^]
Database Normalization Basics[^]
How to use a GridView in ASP.Net.
Accessing Data with the DataSource Controls[^]
除了解决方案1和2之外，我强烈建议您阅读 RDM [ ^ ]和 RDBMS [ ^ ]。
应至少有3张桌子：
课程
CourseID
CourseName
老师
TeacherID
TeacherName
课程
CourseID - For eignKey，链接到课程表
TeacherID - ForeignKey，链接到教师表
阶段
StartDate
EndDate
尝试重新设计数据库并使用两种解决方案中包含的提示。
最终查询应如下所示：
In addition to solution 1 and 2 i strongly recommend to read about RDM[^] and RDBMS[^].
There should be at least 3 tables:
Course
CourseID
CourseName
Teacher
TeacherID
TeacherName
Courses
CourseID - ForeignKey, link to Course table
TeacherID - ForeignKey, link to Teacher table
Stage
StartDate
EndDate
Try to redesign database and use tips comprised in both solutions.
The final query should looks like:
SELECT c.CourseID, c.CourseName, t.TeacherName, m.stage, m.StartDate
FROM Courses AS m LEFT JOIN Course AS C ON m.CourseID = c.CourseID
LEFT JOIN Teacher AS t ON m.TeacherID = t.TeacherID
最重要的是：使用UI而不是SQL来可视化数据/>
数据最佳实践可视化 [ ^ ]
这篇关于如何获得此表的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
