用于检查表中某列数据的C#代码已存在于其他表中
用于检查表中某列数据的C#代码已存在于其他表中
提问于 2020-06-28 19:53:37
我想检查表A中的一条记录，以检查它是否存在于表B中。我计算表B中输入了多少数据。
示例：
A B
------------------------------
John John
Joon Jorge
Jorge Joon
Elizabet Elizabet
Suzan Suzan
Elizabet
Joon
Suzan
John
Elizabet答案应该是这样的：
John = 2
Joon = 2
jorge = 1
Elizabet = 3
Suzan = 2我尝试过的：
using (ImportInvoiceMasterForm.ApplicationSqlConnection2 = new SqlConnection(MainForm.ApplicationDataBase))
{
ImportInvoiceMasterForm.ApplicationSqlConnection2.Open();
using (ImportInvoiceMasterForm.ApplicationSqlCommand1 = new SqlCommand("SELECT * FROM TBL_Stock_Item", ImportInvoiceMasterForm.ApplicationSqlConnection2))
using (ImportInvoiceMasterForm.ApplicationSqlDataReader1 = ImportInvoiceMasterForm.ApplicationSqlCommand1.ExecuteReader())
{
while (ImportInvoiceMasterForm.ApplicationSqlDataReader1.Read())
{
ImportInvoiceMasterForm.GetImportQuantity = 0;
#region Get Import Quantity
using (ImportInvoiceMasterForm.ApplicationSqlConnection1 = new SqlConnection(MainForm.ApplicationDataBase))
{
ImportInvoiceMasterForm.ApplicationSqlConnection1.Open();
using (ImportInvoiceMasterForm.ApplicationSqlCommand = new SqlCommand("SELECT * FROM TBL_Import_Items WHERE ImportItemName='" + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1) + "'", ImportInvoiceMasterForm.ApplicationSqlConnection1))
using (ImportInvoiceMasterForm.ApplicationSqlDataReader = ImportInvoiceMasterForm.ApplicationSqlCommand.ExecuteReader())
{
while (ImportInvoiceMasterForm.ApplicationSqlDataReader.Read())
{
GetImportQuantity += double.Parse(ImportInvoiceMasterForm.ApplicationSqlDataReader.GetValue(4).ToString());
}
}
}
#endregion Get Import Quantity
}
}
}回答 2
Stack Overflow用户
回答已采纳
发布于 2020-06-28 19:59:09
这看起来像是一个聚合。一种方法是join/group by
select a.name, count(b.name)
from a left join
b
on a.name = b.name
group by a.name;left join允许对0进行计数。
如果名称可以在a中重复，则相关子查询是更好的解决方案(假设您希望在a中保留重复项)：
select a.*, (select count(*) from b where b.name = a.name) as cnt
from a;Stack Overflow用户
发布于 2020-06-28 20:05:00
Select A.name, Count(B.name) As Num
FROM A join B
Where B.name=A.name
Group By A.name这将为您提供所需的解决方案
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62622174
复制相关文章
相似问题
腾讯云开发者
