从父表中删除重复的行 [英] Removing duplicate rows from parent table

问题描述

我有一个拥有多对多关系的数据库。表：课程，课本，书籍课程。



一门课程可以有很多书，很多课程都可以有相同的书。



我制作了一个嵌套的gridview显示课程，然后在课程扩展时显示与之相关的每本书。但是在我当前的select查询中，它在Course表中为每本书显示重复的行。当输入一本书时，CourseID将另一本书添加到该课程，但是现在它正在向我的父课程表添加一个重复的行，而不是仅将其添加到教科书表并在课程下显示该课程ID



https://gyazo.com/d576a7835b1727277ceedf7787d35787



上面的链接显示了Course表格网格视图如何显示重复项。如果扩展，有多本书在重复的行下。



我尝试过：



string query =选择DISTINCT *来自课程内连接textBooks on textBooks.CourseID = Course.CourseID;

I have a database with a many to many relationship. Tables: Course, textBooks, BookCourse.

One Course can have many books, many courses can have the same book.

I made a nested gridview showing the Course, and then upon expansion of the course it shows every book associated with it. But in my current select query, It displays duplicate rows in the Course table for each book. When a book is entered, the CourseID to add another book to that Course, but right now it is adding a duplicate row to my parent Course Table instead of just adding it to the textbook table and displaying it under the Course with that CourseID

https://gyazo.com/d576a7835b1727277ceedf7787d35787

The above link shows how the Course table grid view is showing duplicates. if expanded on, there are multiple books under duplicate rows.

What I have tried:

string query = "select DISTINCT * from Course inner join textBooks on textBooks.CourseID = Course.CourseID";

推荐答案

使用DISTINCT，但不要不要用*。因为您正在使用*然后返回所有表中的所有字段，因此DISTINCT可能没有多大帮助。使用DISTINCT但也只选择您需要的字段，而不是所有字段。简单。

Use DISTINCT, but don't use *. Because you are using * then all fields from all tables are being returned and therefore DISTINCT probably doesn't help much. Use DISTINCT but also just select the fields you need, not everything. Simple.

另一种解决方案是尝试不同的表设计方法。不是将书籍与书籍表中的课程相关联，而是设置一个课程表，一个书籍表和一个关系表来更好地设计哪些书籍去哪些课程。你的sql语句必须通过关系表加入，这使初始设置和编码变得更加困难，但使维护更加简单。

Another solution would be to try a different approach to your table design. Instead of a relating a book to a course in the book table, it is a better design to have a table of courses, a table of books, and a relationship table to define which books go to which courses. Your sql statements would have to join through the relationship table which makes the initial set up and coding a bit more difficult but makes maintaining it much more simple.

这篇关于从父表中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！