string strSql = @"declare @sql varchar(8000)
set @sql =
'select ''第''+DATENAME(WEEK,EndDate-1)+''周'' as ''周'',
SUM(case when ActivityType=40 then 1 else 0 end) as ''电询'',
SUM(case when ActivityType=41 then 1 else 0 end) as ''面询'',
SUM(CASE When Sex=1 then 1 else 0 end) as ''男'',
SUM(CASE When Sex=0 then 1 else 0 end) as ''女'',
SUM(CASE When Sex is null then 1 else 0 end) as ''未知'',
SUM(case when EvaluationType=40 then 1 else 0 end) as ''情感家庭'',
SUM(case when EvaluationType is null then 1 else 0 end) as ''其他'',
SUM(CASE When EmployingWay=52 then 1 else 0 end) as ''长期'',
SUM(CASE When EmployingWay=53 then 1 else 0 end) as ''短期'',
SUM(CASE When EmployingWay is null then 1 else 0 end) as ''未登记'''
select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+''''
from (select distinct Department from Crm_CustomerEmployee) as a
set @sql = @sql +' from
(SELECT empid,EndDate,ActivityType,EvaluationType,
(SELECT Sex FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as Sex,
(SELECT EmployingWay FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as EmployingWay,
(SELECT Department FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as Department
from FI_Consult c ) as result
where EndDate between ''" + startDate + @"'' and ''" + endDate + @"''
group by DATENAME(WEEK,EndDate-1)'
exec (@sql)";
// strSql = @"declare @sql varchar(8000)
// set @sql ='SELECT * FROM FI_Consult'
// exec (@sql)";
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=MIS_1119;Integrated Security=True"))
{
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(strSql, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
} }
DataTable tb = ds.Tables[0];上半部分是一条SQL语句,目的是某些字段的汇总情况然后分列显示
但这条语句在MSSQL中执行有结果
可在ASP.NET中获取的时候却什么也得不到,如果把sql语句换成被我注释的那一条的话就没问题有数据。
经我检查,问题出在下面这条语句
select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+''''
from (select distinct Department from Crm_CustomerEmployee) as a
这个语句的功能是循环Department字段使其成为结果表中的列,去掉这一句ADO.NET就能正常调用了,这是为什么?
你那个部门那那么多单引号,看的眼睛好疼啊
这里print出来是这样:
SUM(CASE When Department='HR' then 1 else 0 end) as 'HR',SUM(CASE When Department='IT部' then 1 else 0 end) as 'IT部',SUM(CASE When Department='财务部' then 1 else 0 end) as '财务部',SUM(CASE When Department='促进步' then 1 else 0 end) as '促进步',SUM(CASE When Department='培训部' then 1 else 0 end) as '培训部',SUM(CASE When Department='项目部' then 1 else 0 end) as '项目部',SUM(CASE When Department='咨询部' then 1 else 0 end) as '咨询部' from
N年没用过case...then...了