如何在存储过程中组合两个表以在aspgrid视图中的数据字段中传递别名

问题描述

您好我正在通过从一个表中获取数据并从其他表中更新详细信息来处理asp Grid



我尝试过：

 <   pre  >  
修改程序[dbo]。[Sampe3]（@ years int，@ months int）
 as 
 
; t1为（
 SELECT DeptID，COUNT（EmpID）AS TotalHeadCount FROM EmployeeDetails 
 WHERE（datepart（yyyy，DOJ）在2005和@years之间）和Status = 0 
 group by DeptID 
），
 t2 as（
 SELECT DeptID，COUNT（EmpID）AS NewJoinees FROM EmployeeDetails AS EmployeeDetails_7 
 WHERE（DATEPART（yyyy，DOJ）= @years ）AND（DATEPART（mm，DOJ）= @months）和datepart（mm，DOJ）> = @months和Status = 0 
 group by DeptID 
），
 t3 as（
 SELECT DeptID，COUNT（EmpID）AS从EmployeeDetails辞职AS EmployeeDetails_7 
 WHERE（DATEPART（ yyyy，deactivate）= @years）AND（DATEPART（mm，deactivate）= @months）和datepart（mm，deactivate）> = @months和Status = 1 group by DeptID 
）
选择t1 .DeptID，CASE当TotalHeadCount为NULL然后'0'ELSE TotalHeadCount END AS TotalHeadCount，例如NewJoinees为NULL然后'0'ELSE NewJoinees END为NewJoinees，CASE WHEN Resigned IS NULL那么'0'ELSE Resigned END AS Resigned，''作为ToBeHired，''作为OpenPositions''作为状态
来自t1全外连接t2 on t1.DeptID = t2.DeptID 
全外连接t3 on t1.DeptID = t3.DeptID 
 union 
选择'ZTotal'为total，''为TotalHeadCount，'为NewJoinees'，'as Resigned'，'为ToBeHired'，'为OpenPositions'，'为Status 
 
（更新RecruitmentDetails set Tobehired = @ Tobehired，OpenPosition = @ OpenPosition，Status = @ Status）As Update 

< asp：GridView ID =grdvEmployeeLeaveUpdaterunat =serverDataKeyNames =
 class =table table-bordere d table-striped table-hoverDataSourceID =SqlDataSourceHeadCount
 OnRowDataBound =grdvEmployeeLeaveUpdate_RowDataBoundWidth =100％
 OnRowDeleting =grdvEmployeeLeaveUpdate_RowDeleting
 OnRowEditing =grdvEmployeeLeaveUpdate_RowEditing
 OnRowUpdating =grdvEmployeeLeaveUpdate_RowUpdating
 OnRowCancelingEdit =grdvEmployeeLeaveUpdate_RowCancelingEdit
 OnPageIndexChanging =grdvEmployeeLeaveUpdate_PageIndexChanging> 
 
< Columns> 
< asp：BoundField DataField =DeptIDHeaderText =Cost CenterReadOnly =trueControlStyle-Width =50px/> 
< asp：BoundField DataField =TotalHeadCountHeaderText =HeadCountReadOnly =trueControlStyle-Width =50px/> 
< asp：BoundField DataField =NewJoineesHeaderText =AdditionsReadOnly =trueControlStyle-Width =50px/> 
< asp：BoundField DataField =ResignedHeaderText =SeparationsReadOnly =trueControlStyle-Width =50px/> 
< asp：BoundField DataField =ToBeHiredHeaderText =待雇用ControlStyle-Width =50px/> 
< asp：BoundField DataField =OpenPositionsHeaderText =Open PositionControlStyle-Width =50px/> 
< asp：BoundField DataField =StatusHeaderText =StatusControlStyle-Width =50px/> 
< asp：CommandField HeaderText =UpdateShowEditButton =TrueControlStyle-Width =50px/> 
 
< / Columns> 
< HeaderStyle>< / HeaderStyle> 
< / asp：GridView> 

< asp：SqlDataSource ID =SqlDataSourceHeadCount runat =serverConnectionString =<％$ ConnectionStrings：TimeSheetConnectionString2％> 
 SelectCommandType =StoredProcedureSelectCommand =Sampe3> 
< SelectParameters> 
< asp：ControlParameter ControlID =ddlMonthsName =monthsType =Int32/> 
< asp：ControlParameter ControlID =ddlyearName =yearsType =Int32/> 
< / SelectParameters> 
< / asp：SqlDataSource> 

解决方案

这篇关于如何在存储过程中组合两个表以在aspgrid视图中的数据字段中传递别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！