遍历一个sql server表，在另一个表中计数值并插入数据. C#/SQL服务器 [英] Looping through an sql server Table, counting values and inseritng data in another table. C#/SQL erver

问题描述

这是我数据库中表的结构.我需要在首选项列"中检查每个学生的首选项"值，并在学生表"中插入组ID"，例如螺柱A"，并在首选项列"中插入所有条目，以螺柱A".我需要使用循环或类似方法生成组ID"，但我必须检查学生表"中是否有最少5个和最多8个学生可以具有相同的组ID".谁能提供一些类似问题的代码示例.

This is the structure of tables in my database. I need to Check Preferences value for each Student in "Preferences Column" and insert a "Group ID" ins "Students Table" for, for example "Stud A" and and all entries in "Preferences column" for "Stud A". I need to generate "group ID" using a Loop or something but i have to check that in "Students Table" minimum 5 and maximum 8 students can have same "group ID". Can anyone provide some code examples for similar problems.

Preference_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)
--------------------------
    |student | Group ID/Name |
    --------------------------
    Stud A   |    
    Stud B   |  
    Stud C   |  
    Stud D   |  
    Stud E   |  
    Stud F   |  
    --------------------------

推荐答案

您好，如果您还记得最后一个问题，那么每个学生都喜欢三个选择.最喜欢的学生并给他们评分，下面是sql代码，您需要继续设置学生组中的学生，实际上，小组是根据最喜欢的学生找到的，您只需将他们说的学生组中喜欢:

Hi, if you remember your last question with three Choices for preferring for each students,i rated for most preferred students and give them rate, and the sql code is below , you need to continue and set down the students in their group,in fact groups are found base on most preferred students , you only need set the students in their group they said prefer:

<pre>--table for  rate of preference for prefered students
if OBJECT_ID(N'StudentCoreRate','U') !=0
drop table StudentCoreRate 
create table StudentCoreRate (StudentID int, Rate decimal(10,2))
--drop table #FirstPrefer
--drop table #SecondPrefer
--drop table #ThirdPrefer

select  FirstPrefer.Prefere1 as sid, Count(FirstPrefer.Prefere1)  FirstPrefer into #FirstPrefer
from Preference FirstPrefer 
group by FirstPrefer.Prefere1
--select * from #FirstPrefer


--update more prefered students by thier ID as thier groupID
insert into StudentCoreRate select Students.ID , FirstPrefer
from Students  
inner join #FirstPrefer on Students.ID = #FirstPrefer.SID
where FirstPrefer > 1

select  SecondPrefer.Prefere2 sid, Count(SecondPrefer.Prefere2)  SecondPrefer into #SecondPrefer
from  Preference SecondPrefer 
group by SecondPrefer.Prefere2

--I divide secondPrefer into 2 as it is SecondPrefer
--In this Update add secondPrefered to students rate who get rate from first prefered, and in next insert i add students who
--only have rated as second prefere or third prefered
update StudentCoreRate set Rate = Rate + cast(SecondPrefer as decimal)/2
from #SecondPrefer 
inner join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where SecondPrefer > 1
--i add students who only have rated as second prefere or third prefered
insert into StudentCoreRate select Students.ID , cast(#SecondPrefer.SecondPrefer as decimal)/2--,  StudentCoreRate.StudentID 
from Students  
inner join #SecondPrefer on Students.ID = #SecondPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where StudentCoreRate.StudentID is null
and #SecondPrefer.SecondPrefer > 1



select  ThirdPrefer.Prefere3 sid, Count(ThirdPrefer.Prefere3)  ThirdPrefer into #ThirdPrefer
from Preference ThirdPrefer 
group by ThirdPrefer.Prefere3



update StudentCoreRate set Rate = Rate + cast(ThirdPrefer as decimal)/3
from #ThirdPrefer 
inner join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where ThirdPrefer > 1
--i add students who only have rated as second prefere or third prefered
insert into StudentCoreRate select Students.ID , cast(#ThirdPrefer.ThirdPrefer as decimal)/3
from Students  
inner join #ThirdPrefer on Students.ID = #ThirdPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where StudentCoreRate.StudentID is null
and ThirdPrefer > 1

--then you need the rate of perefering for students who prefered more prefered students

select * from StudentCoreRate
order by rate desc
select * from #SecondPrefer
select * from #ThirdPrefer
select * from Preference

drop table #FirstPrefer
drop table #SecondPrefer
drop table #ThirdPrefer

这篇关于遍历一个sql server表，在另一个表中计数值并插入数据. C#/SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！