检查sql server表中的列值，比较它们并插入不同的表中 [英] check column values in sql server table compare them and insert in different table

问题描述

我的数据库中有两张桌子



（Prefrences_Table）

I have two tables in my database

(Prefrences_Table)

--------------------------
|student | Preferences |
--------------------------
Stud A   |  Stud B  
Stud A   |  Stud C
Stud B   |  Stud E
Stud B   |  Stud A
Stud C   |  Stud F
Stud F   |  Stud B
--------------------------

（Prefrences_Table）

(Prefrences_Table)

--------------------------
    |student | Group ID/Name |
    --------------------------
    Stud A   |    
    Stud B   |  
    Stud C   |  
    Stud D   |  
    Stud E   |  
    Stud F   |  
    --------------------------

我要求每个学生最多给我7个名字，他们想和谁合作。然后在帐户中获取这些信息尽可能多地做出选择并创建一个团队。



这些是我必须遵循的规则来创建团队。



首先，我将检查Stud A是否在他的偏好列表中添加了Stud B，并且stud B在他的偏好中添加了stud A，如果两者都是他们互相添加了我想在团队/团队中添加它们（为所有学生这样做）。第二，检查单侧偏好，Stud A在他的偏好中添加了Stud B但是B没有，并将它们添加到组中。第三，将所有学生添加到随机组中。每个团队/团队可以有5-8名学生。



我可以使用select t.student，t1.student来自Prefrences_Table t inner join Prefrences_Table t1 on t.student = t1.preferences和t.preferences = t1.student和t.student< t1.student找到共同的偏好。



如何创建组号，然后将它们插入学生表中，检查最少5名和最多8名学生是否有此ID。 br />


将通过单击应用程序中的按钮来创建组

I am asking each student to give me maximum 7 names, who they want to team up with. then taking this information in account fulfill as many choices as possible and create a team.

These are the rules i have to follow to create teams.

First i will check if "Stud A" has added "Stud B" in his Preferences list, and "stud B" has also added "stud A" in his preference, if both of them have added each other i want to add them in group/team (Do this for all students). Second, Check for one sided preferences, "Stud A" has added "Stud B" in his Preferences but stud B hasn't, and add them in group. Third add all students without preference to random Groups. Each group/team can have 5-8 students.

I can use select t.student, t1.student from Prefrences_Table t inner join Prefrences_Table t1 on t.student = t1.preferences and t.preferences = t1.student and t.student < t1.student to find mutual preferences.

How do i create group numbers and than insert them in student table checking that minimum 5 and maximum 8 students have this id.

Groups are going to be created by clicking a button in the application

推荐答案

您需要创建一个关于如何创建组的规则集（您需要某种优先级来保证组成员的保证重叠）。



您可以创建一个VIEW来计算数量每个学生的选择和（可能）按计数，降序排列，给你一个列表开始。您可以通过使视图成为链接选择彼此的学生的内部联接的结果（但是这将省略不属于该组的那些 - 所以想想UNION也可以获得列表中的那些）来进一步实现这一点。



现在，您创建一个表格链接学生和一个组号或名称（我猜您还没有定义）。



创建组时，可以在SUBQUERY中使用它来从VIEW中删除对这些学生的引用（子查询显然应该是视图的一部分）。您正在使用子查询来删除已经在小组中的学生。



当您用完了足够多的小组时，您将需要一个使用以下成员的规则 - 小组和单身人士。



但是，拥有规则集是至关重要的：你需要某种清晰的方式来开始制作你的小组以及如何处理冲突和其他例外。

You need to create a set of rules as to how you will create groups (you need some sort of priority for the guaranteed overlaps of group members).

You can create a VIEW giving the count of selections for each students and (perhaps) order by count, descending, giving you a list to start from. You can take this further by making the view the result of an INNER JOIN that links students who select one another (but that will omit those not part of the group - so think UNION to get those on the list, too).

Now, you create a table linking students and a group number or name (something I guess you don't yet have defined).

When you create a group, you can use it in a SUBQUERY to remove references to those students from your VIEW (the subquery should, obviously, be part of your view). You are using the subquery to remove students already in groups.

When you run out of large-enough groups, you'll need a rule for using members of too-small groups and singletons.

Having a rule-set, however, is essential: you need some sort of clarity in how to start making your groups and how to handle conflicts and other exceptions.

这篇关于检查sql server表中的列值，比较它们并插入不同的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！