如何在datatable中执行SQL查询。

本文介绍了如何在datatable中执行SQL查询。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一些情况。其中我将数据从数据库填充到datatable，然后我想在数据表中执行sum和group by的一些条件。那么在datatable中执行sql select查询的目的是什么呢。 我的以下查询在datatable中生成输出。或者是否可以使用select * into ........语句创建具有以下查询的临时表。 我尝试过： 选择 MasterCustomer.CustType as ' Identity Name'，MasterCustomer.companyCollegeName as ' 联系人姓名'，MasterCustomer.CustomerName as ' 客户名称，SUM（TempOrderDetailNew。 BookQuantity） As ' OTS总账面数量， Null 作为 ' QuickBilling Tota l预订数量'， Null 作为 ' ReturnOrder总图书数量 来自 MasterCustomer 内部 加入 MasterState 开启 MasterCustomer.State = MasterState.StateID 内部 加入 MasterCity 开启 MasterCustomer.CityID = MasterCity.CityID 内部 加入 TempOrderNew 开启 MasterCustomer.CustomerCode = TempOrderNew.CustomerCode 内部 加入 TempOrderDetailNew On TempOrderNew.BillNumber = TempOrderDetailNew.BillNo 其中（MasterCustomer.UniversityName = ' UU1 ' 或 MasterCustomer.UniversityName 赞 ' UU1 - ％' 或 MasterCustomer.UniversityName 赞 ' ％ - UU1' 或 MasterCustomer.UniversityName 类似 ' ％ - UU1-％ '）和 MasterCustomer.CustType = ' BookSeller' 组 按 MasterCustomer.CustType，MasterCustomer.companyCollegeName，MasterCustomer。 CUS tomerName union 全部 选择 MasterCustomer.CustType 为 ' Identity Name'，MasterCustomer.companyCollegeName as ' 联系人姓名，MasterCustomer.CustomerName as ' 客户名称，空 作为 ' OTS总账面数量，SUM（QuatOerDet.BookQuantity） As ' QuickBilling总图书数量， Null 正如 ' ReturnOrder总图书数量 From MasterCustomer 内部 加入 MasterState On MasterCustomer.State = MasterState.StateID 内部 加入 MasterCity On MasterCustomer.CityID = MasterCity.CityID Inner Join [QuotOrder] On MasterCustomer.CustomerCode = [QuotOrder] .CustomerCode Inner 加入 [QuatOerDet] 开启 [QuotOrder] .BillNumber = [QuatOerDet] .BillNo 其中（MasterCustomer.UniversityName = ' UU1' 或 MasterCustomer.UniversityName 赞 ' UU1 - ％' 或 MasterCustomer.UniversityName 赞 ' ％ - UU1' 或 MasterCustomer.UniversityName 赞 ' ％ - UU1 - ％'）和 MasterCustomer.CustType = ' BookSeller' 和 [QuotOrder] .TypeOfBill！= ' QUOT' 组 按 MasterCustomer.CustType，MasterCustomer.companyCo llegeName，MasterCustomer.CustomerName union 全部 选择 SUM（BookReturnDetailNew.BookQty）作为 ' ReturnOrder总图书数量 来自 MasterCustomer 内部 加入 MasterState 开启 MasterCustomer.State = MasterState.StateID 内部 加入 MasterCity 开启 MasterCustomer.CityID = MasterCity。 CityID 内部 加入 ReturnBookNew 开启 MasterCustomer.CustomerCode = ReturnBookNew.CustomerCode 内部 Join BookReturnDetailNew On ReturnBookNew.CreditnoteNo = BookReturnDetailNew.CreditnoteNo 其中（MasterCustomer.UniversityName = ' UU1' 或 MasterCustomer.UniversityName 赞 ' UU1 - ％' 或 MasterCustomer.UniversityName 赞 ' ％ - UU1' 或 MasterCustomer.UniversityName 喜欢 ' ％ - UU1 - ％'）和 MasterCustomer.CustType = ' BookSeller' 组 按 MasterCustomer.CustType，MasterCustomer.companyCollegeName，MasterCustomer.CustomerName 解决方案 您可以使用 DataTable.Select方法（字符串）（System.Data） [ ^ ] 我重新读了你的问题并发现 引用：我想在数据表中为sum和group执行一些条件 数据表上的Select只是过滤器不会帮助您分组或总和。就个人而言，我会回到数据库获取该信息。否则你需要逐步完成结果，例如 var sum = 0; foreach（dt.Rows中的DataRow dr） { sum + = int.Parse（dr.ItemArray [3] .ToString（））; } 您可以将其中一部分转换为Linq，例如：以上与 var sum = dt.Rows.Cast< DataRow>（）。Sum（dr => int.Parse（dr.ItemArray [3] .ToString（）））; 看看这里：如何：执行返回行的存储过程 [ ^ ] i have some situation. in which i populate data from database to datatable and then i want to execute some conditon for sum and group by in datatable. so what is the peocedure to execute sql select query in datatable.my following query generate a output in datatable. or is it possible to create temp table with following query using select * into........ statement.What I have tried:Select MasterCustomer.CustType as 'Identity Name', MasterCustomer.companyCollegeName as 'Contact Person Name', MasterCustomer.CustomerName as 'Customer Name', SUM(TempOrderDetailNew.BookQuantity) As 'OTS Total Book Quantity', Null As 'QuickBilling Total Book Quantity', Null As 'ReturnOrder Total Book Quantity'From MasterCustomerInner Join MasterState On MasterCustomer.State = MasterState.StateIDInner Join MasterCity On MasterCustomer.CityID = MasterCity.CityIDInner Join TempOrderNew On MasterCustomer.CustomerCode = TempOrderNew.CustomerCodeInner Join TempOrderDetailNew On TempOrderNew.BillNumber = TempOrderDetailNew.BillNoWhere (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%') and MasterCustomer.CustType = 'BookSeller'Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerNameunion AllSelect MasterCustomer.CustType as 'Identity Name', MasterCustomer.companyCollegeName as 'Contact Person Name', MasterCustomer.CustomerName as 'Customer Name', Null As 'OTS Total Book Quantity', SUM(QuatOerDet.BookQuantity) As 'QuickBilling Total Book Quantity', Null As 'ReturnOrder Total Book Quantity'From MasterCustomerInner Join MasterState On MasterCustomer.State = MasterState.StateIDInner Join MasterCity On MasterCustomer.CityID = MasterCity.CityIDInner Join [QuotOrder] On MasterCustomer.CustomerCode = [QuotOrder].CustomerCodeInner Join [QuatOerDet] On [QuotOrder].BillNumber = [QuatOerDet].BillNoWhere (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%') and MasterCustomer.CustType = 'BookSeller' and [QuotOrder].TypeOfBill != 'QUOT'Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerNameunion AllSelect SUM(BookReturnDetailNew.BookQty) As 'ReturnOrder Total Book Quantity'From MasterCustomerInner Join MasterState On MasterCustomer.State = MasterState.StateIDInner Join MasterCity On MasterCustomer.CityID = MasterCity.CityIDInner Join ReturnBookNew On MasterCustomer.CustomerCode = ReturnBookNew.CustomerCodeInner Join BookReturnDetailNew On ReturnBookNew.CreditnoteNo = BookReturnDetailNew.CreditnoteNoWhere (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%') and MasterCustomer.CustType = 'BookSeller'Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerName 解决方案 You can use DataTable.Select Method (String) (System.Data)[^][EDIT] I re-read you question and spotted the Quote:i want to execute some conditon for sum and group by in datatableThe Select on the datatable is only a filter so will not help you with group by or sum. Personally, I would just go back to the database for that information. Otherwise you will need to step through the results e.g. var sum = 0;foreach (DataRow dr in dt.Rows){ sum += int.Parse(dr.ItemArray[3].ToString());}You can convert some of this to Linq e.g. the above is the same as var sum = dt.Rows.Cast<DataRow>().Sum(dr => int.Parse(dr.ItemArray[3].ToString()));Take a look here: How to: Execute a Stored Procedure that Returns Rows[^] 这篇关于如何在datatable中执行SQL查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！