遍历一个sql server表，在另一个表中计数值并插入数据. C#/SQL服务器

问题描述

这是我数据库中表的结构.我需要在首选项列"中检查每个学生的首选项"值，并在学生表"中插入组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   |
    --------------------------

推荐答案

<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服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！