如何在datatable中执行SQL查询。 [英] How to execute SQL query in datatable.

问题描述

我有一些情况。其中我将数据从数据库填充到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 < span class =code-keyword> 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 赞 ' < span class =code-string>％ -  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 MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join TempOrderNew On MasterCustomer.CustomerCode = TempOrderNew.CustomerCode 
Inner Join TempOrderDetailNew On TempOrderNew.BillNumber = TempOrderDetailNew.BillNo  
Where (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 

union All
Select 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 MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join [QuotOrder] On MasterCustomer.CustomerCode = [QuotOrder].CustomerCode 
Inner Join [QuatOerDet] On [QuotOrder].BillNumber = [QuatOerDet].BillNo  
Where (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.CustomerName 

union All
Select SUM(BookReturnDetailNew.BookQty) As 'ReturnOrder Total Book Quantity'  
From MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join ReturnBookNew On MasterCustomer.CustomerCode = ReturnBookNew.CustomerCode 
Inner Join BookReturnDetailNew On ReturnBookNew.CreditnoteNo = BookReturnDetailNew.CreditnoteNo  
Where (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 datatable

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