c# - SQL 语句中的 Seats Academy 计算
问题描述
我的分配算法需要基于每个学院名额的剩余席位。
这是我的表格的插图:
tbl学院
Acad_id name numberOfSeats
------------------------------
1 A 2
2 B 2
3 C 1
4 D 5
5 E 3
tbl学生
stud_Id name `stud_purcentage` `stud_result` acad_id
----------------------------------------------------------
1 Alex 100 `Pass`
2 Lee 80.5 `Pass`
3 Lea 40.3 `Fail`
4 Loane 10 `Fail`
5 john 50 `Pass`
tblAcademy_selection
stud_id Acad_id order_preference
--------------------------------
1 1 1
1 3 2
4 3 1
4 2 2
4 4 3
我的算法:
SqlConnection dbcon = new SqlConnection(_conString);
SqlCommand scmd = new SqlCommand();
scmd.CommandText = "SELECT stud_Id,stud_fname,stud_purcentage,stud_totalMarks FROM tblStudent where stud_result='Pass' order by stud_purcentage Desc";
scmd.Connection = dbcon;
SqlDataAdapter da = new SqlDataAdapter(scmd);
DataTable dt = new DataTable();
dbcon.Open();
da.Fill(dt);
string[] array = new string[dt.Rows.Count];
// foreach (DataRow row in dt.Rows)
for (int a = 0; a < dt.Rows.Count; a++)
{
array[a] = dt.Rows[a]["stud_Id"].ToString();
SqlCommand scmd2 = new SqlCommand();
scmd2.CommandText = "select acad_Id,stud_Id from(SELECT tas.*, DENSE_RANK() OVER (PARTITION BY stud_id ORDER BY order_preference) AS row_id FROM tblAcademy ta JOIN tblAcademy_Selection tas ON ta.acad_Id = tas.acad_Id WHERE ta.numberOfSeats > 0 and stud_Id IN('" + array[a] + "')) DTL WHERE row_id = 1";
scmd2.Connection= dbcon;
SqlDataAdapter da2 = new SqlDataAdapter(scmd2);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
string[] array2 = new string[dt2.Rows.Count];
string[] array3 = new string[dt2.Rows.Count];
for (int a2 = 0; a2 < dt2.Rows.Count; a2++)
{
String acad_Id = dt2.Rows[a2]["acad_Id"].ToString();
array2[a2] = dt2.Rows[a2]["stud_id"].ToString();
//string[,] array3 = new string[array2[a2],];
SqlCommand scmd3 = new SqlCommand();
scmd3.CommandText = "update tblStudent set acad_id='" + acad_Id + "' where stud_Id='" + array2[a2] + "'";
scmd3.Connection = dbcon;
scmd3.ExecuteNonQuery();
//ListBox1.Items.Add(array5);
}
}
getStudent();
在scmd2
我的 sql 上正在寻找一个静态列 numberOfSeats WHERE ta.numberOfSeats > 0
(学院配额),这不是我要寻找的。
我需要的是where
条件基于计算 tblStudent 中每个 acadID 的总行数 - tblAcademy 中每个 acadID 的 numberOfSeat (学院配额)。
下面是获取剩余席位sql代码的代码:
SELECT distinct ta.acad_Id, numberOfSeats - Count(ts.acad_id) OVER (
PARTITION BY ta.acad_Id
) as remainingSeat
FROM tblAcademy ta,
tblStudent ts
where ta.acad_Id = ts.acad_Id or ts.acad_Id is null
我很难将 sql 代码定位smcd2 command text
到remainingSeat
别名而不是ta.numberOfSeats
解决方案
看来你多虑了。要查找剩余座位数,您只需要一个相关的子查询
SELECT
ta.acad_Id,
ta.numberOfSeats - ts.cn as remainingSeat
FROM tblAcademy ta
OUTER APPLY (
SELECT COUNT(*) AS cn
FROM tblStudent ts
WHERE ta.acad_Id = ts.acad_Id
) ts
WHERE ta.numberOfSeats - ts.total > 0;
-- or as a pre-grouped joined
SELECT
ta.acad_Id,
ta.numberOfSeats - ts.cn as remainingSeat
FROM tblAcademy ta
LEFT JOIN (
SELECT
ts.acad_Id,
COUNT(*) AS cn
FROM tblStudent ts
) ts ON ta.acad_Id = ts.acad_Id
WHERE ta.numberOfSeats - ts.cn;
目前尚不清楚您要对查询执行什么操作scmd2
旁注:
- 始终参数化您的查询,不要将数据注入其中。
- 不要使用旧式
,
交叉连接,它们在 30 年前已被弃用。
推荐阅读
- python - 如何在同步代码的龙卷风中使用 Process Pool Executor?
- python-3.x - 通过基于条件生成可能的组合将数据帧拆分为多个数据帧
- python - Kivy 使用 RecycleView 的 CheckBox 问题
- c# - 来自主模型的 C# 子模型
- c++ - 在 .cpp 文件中定义整数常量的正确方法
- ios - CreateML 迁移学习模型不会在 iOS 模拟器上运行
- java - 使用 @ManyToMany 复制实体
- pandas - Pandas 创建一个新的 C 列,其值为 A 列但没有连字符
- json - 使用 PowerShell 解析和操作 Json 文件中的日期
- spring - 我可以获得与@OneToMany 相关的对象列表吗?