检查每行表中包含多少列的值 [英] Check how many columns contain value in each row of table

问题描述

我在MSSQL数据库中有这两个表。



学生偏好表（实际表有7个偏好）

I have these two tables in MSSQL database.

Student Preferences Tables (actual table has 7 preferences)

------------------------------------------------------------------------------
   Student Name |   Preference 1|  Preference 2 | Preference 3  |Group ID
   -----------------------------------------------------------------------
   Student A    |   Student G   |   Student F   |  Student G    |
   Student B    |   Student H   |   Student K   |  Student L    |
   Student C    |   Student I   |   Student A
   Student D    |   Student H   |   Student K
   Student E
   Student F
   Student G
   Student H
   Student I
   Student J
   Student K
   Student L
   -----------------------------------------------------------------------------

团体表

Groups Table

|Group ID |
    ---------
   1
   2
   3
   ----------

我根据学生输入的偏好（首选合作伙伴/队友）创建学生小组。每组可以有5-8名学生。不是所有学生都会输入偏好。要创建一个组，我将检查已输入4-7个偏好的学生并将其添加到组中。例如，学生A已输入4个偏好。包括学生A在内，我有5名学生团体所需的最低人数。所以，我想在Groups table中插入并检索该号码并在每个学生的Group ID列中插入。如果学生输入的偏好少于4个，我将不得不创建一个新的小组ID并分配给学生，但我还必须检查每个小组条件的5-8名学生是否未被违反。



有人可以指导我这个。如何检查超过4个首选项列包含数据（名称）的行？如果存在超过4个首选项的学生，那么我如何在组表（组ID（auto-inc））中插入新记录并将其检索回以插入学生偏好表中。如何在插入组ID之前检查5-8名学生的情况是否违反？我试图找出如何以最简单的方式解决这些问题（使用c＃或SQL）？我正在用C＃编写应用程序。

I am creating groups of student based on preferences(preferred partners/team mates) entered by student. Each group can have 5-8 students.Not all students will enter preferences. To create a group i will check for the students who have entered 4-7 preferences and add them to a group. For example, Student A has entered 4 preferences. Including student A now i have 5 students minimum number required for group. So, i would like to insert in "Groups table" and retrieve that number and insert in "Group ID" column for each student. In cases if student has entered less than 4 preferences, i will have to create a new "group id" and assign to students but i also have to check that 5-8 students per group condition is not breached.

Can someone please guide me on this. How to check for rows where more than 4 preference columns contain data(names)? If students with more than 4 preferences exist, than how can i insert new record in Groups table(Group ID (auto-inc)) and retrieve it back to insert in Student Preference table. How can i check 5-8 students condition is not breaching before inserting group id? I am trying to find out how can these problems be solved (using c# or SQL) in easiest way? I am writing application in C#.

推荐答案

你的组织不正确 - 这是一个关系表问题。



表1：每个学生输入一次具有唯一标识符（如身份字段）

表2：对学生的参考（第1栏）和首选合作伙伴的身份（第2栏） ）。此表中可以有多个记录，在第1列中具有相同的值。（I，E。表1为表2提供外键）



现在你可以对表2中具有分组条款的学生轻松获取计数，回答您有足够偏好的问题。 （您甚至可以通过计算第2列来计算谁是最受欢迎的）



您应该确保学生不能选择自己作为合作伙伴或自动强制选择这些选择可以使用表2中的触发器来完成。

You've got it organized incorrectly - this is a relational table problem.

Table 1: Each student entered once with a unique identifier (like an identity field)
Table 2: A reference to the student (col 1) and a preferred partner's identity (col 2). There can be multiple records in this table with the same value in col 1. (I,E. table one supplies a foreign key for table 2)

Now you can easily get counts for any student with Group-by clause on table 2, answering you problem of those that have 'enough' preferences. (You could even count who's most-preferred by counting col 2)

You should insure that a student cannot select themselves as partner OR force this selection automatically, either of these can be done with triggers on table 2.

这篇关于检查每行表中包含多少列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！