这是执行SQL语句的方法 public DataTable GetData(string sql, string ATableName, SqlParameter[] sqlparam)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
SqlCommand sqlComm = new SqlCommand(sql, GetConnection());
for (int j = 0; j < sqlparam.Length; j++)
{
sqlComm.Parameters.Add(sqlparam[j]);
}
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds, ATableName);
}
dt = ds.Tables[0];
}
catch (Exception e)
{
throw e;
}
finally
{
ColseConnection();
}
return dt;
}这是传的sql语句if object_id(N'tempdb..#temp_6e8866c7d2ff438685f272ea0f5b8644',N'U') is not null drop table #temp_6e8866c7d2ff438685f272ea0f5b8644;
Select * into #temp_6e8866c7d2ff438685f272ea0f5b8644
from
(
Select DISTINCT a.[组织编号] as col, row_number()over(order by b.n desc)RowNo, ISNULL(b.n,0) as n
From [View_Statistics] a
LEFT join
(
Select DISTINCT [组织编号] as col, COUNT(*) as n
from [View_Statistics]
Where [报修时间] between @beginDate and @endDate and
[组织编号] in (@Param_0_0,@Param_0_1,@Param_0_2,@Param_0_3,@Param_0_4,@Param_0_5)
group by [组织编号]
) b
on a.[组织编号] = b.col
group by a.[组织编号],b.n) T;
declare @sumNum int;
Set @sumNum = (select SUM(n) from #temp_6e8866c7d2ff438685f272ea0f5b8644);
Select col,n,Convert(decimal(18,2),CAST(n*100 AS FLOAT)/@sumNum) as ratio,Convert(decimal(18,2),
CAST((select sum(n) from #temp_6e8866c7d2ff438685f272ea0f5b8644 where RowNo<=A.RowNo)*100 AS FLOAT) /@sumNum ) as total
From #temp_6e8866c7d2ff438685f272ea0f5b8644 A
Group by RowNo,col,n
Order by n desc;
if object_id(N'tempdb..#temp_6e8866c7d2ff438685f272ea0f5b8644',N'U') is not null drop table #temp_6e8866c7d2ff438685f272ea0f5b8644;这是传的参数
我把参数里的值取出来,放在数据库里面执行得到的结果为:
然后我在程序里面执行的结果为:
在数据库里面得到的是正确的结果,
为啥程序里确有问题呢?
我就郁闷了,我这是哪里错了呀?
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
SqlCommand sqlComm = new SqlCommand(sql, GetConnection());
for (int j = 0; j < sqlparam.Length; j++)
{
sqlComm.Parameters.Add(sqlparam[j]);
}
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds, ATableName);
}
dt = ds.Tables[0];
}
catch (Exception e)
{
throw e;
}
finally
{
ColseConnection();
}
return dt;
}这是传的sql语句if object_id(N'tempdb..#temp_6e8866c7d2ff438685f272ea0f5b8644',N'U') is not null drop table #temp_6e8866c7d2ff438685f272ea0f5b8644;
Select * into #temp_6e8866c7d2ff438685f272ea0f5b8644
from
(
Select DISTINCT a.[组织编号] as col, row_number()over(order by b.n desc)RowNo, ISNULL(b.n,0) as n
From [View_Statistics] a
LEFT join
(
Select DISTINCT [组织编号] as col, COUNT(*) as n
from [View_Statistics]
Where [报修时间] between @beginDate and @endDate and
[组织编号] in (@Param_0_0,@Param_0_1,@Param_0_2,@Param_0_3,@Param_0_4,@Param_0_5)
group by [组织编号]
) b
on a.[组织编号] = b.col
group by a.[组织编号],b.n) T;
declare @sumNum int;
Set @sumNum = (select SUM(n) from #temp_6e8866c7d2ff438685f272ea0f5b8644);
Select col,n,Convert(decimal(18,2),CAST(n*100 AS FLOAT)/@sumNum) as ratio,Convert(decimal(18,2),
CAST((select sum(n) from #temp_6e8866c7d2ff438685f272ea0f5b8644 where RowNo<=A.RowNo)*100 AS FLOAT) /@sumNum ) as total
From #temp_6e8866c7d2ff438685f272ea0f5b8644 A
Group by RowNo,col,n
Order by n desc;
if object_id(N'tempdb..#temp_6e8866c7d2ff438685f272ea0f5b8644',N'U') is not null drop table #temp_6e8866c7d2ff438685f272ea0f5b8644;这是传的参数
我把参数里的值取出来,放在数据库里面执行得到的结果为:
然后我在程序里面执行的结果为:
在数据库里面得到的是正确的结果,
为啥程序里确有问题呢?
我就郁闷了,我这是哪里错了呀?
解决方案 »
- 下面的代码有没有什么简单的形式?
- 正则表达式(车牌号)
- 拷贝文件出现了乱码
- 主窗体和多个子窗体,运行时子窗体无缝嵌入到主窗体内显示(不是弹出式窗体)是如何实现的?
- 求助物资管理信息系统
- 对一个sql sever表,怎样判断一个字段是允许空值还是不允许空值?
- 如何获得鼠标所在屏幕点的像素?
- 水晶报表如何翻转(90。)?????高手帮忙!!
- J:\CAD#\DXFViewer\Form1.cs(121): 无法创建抽象类或接口“System.Windows.Forms.FileDialog”的实例
- 如何在ListBox中分多列显示。
- 【100分】求助!关于代码写哪里的问题!菜鸟提问
- VS2008团队资源管理器TFS怎么使用啊?求简单的教程,谢谢!
Select DISTINCT [组织编号] as col, COUNT(*) as n
from [View_Statistics]
Where [报修时间] between @beginDate and @endDate
and [组织编号] in (@Param_0_0,@Param_0_1,@Param_0_2,@Param_0_3,@Param_0_4,@Param_0_5)
group by [组织编号]获取参数的就是这个一句
然后才运行
da.Fill(ds, ATableName)
不管是sql语句 还是 参数值都完全一样,
SQL Server Profiler执行语句exec sp_executesql N'if object_id(N''tempdb..#temp_d74f87048c6a4f6b8572ccb2299d5ac7'',N''U'') is not null drop table #temp_d74f87048c6a4f6b8572ccb2299d5ac7;Select * into #temp_d74f87048c6a4f6b8572ccb2299d5ac7 from (Select DISTINCT a.[组织编号] as col, row_number()over(order by b.n desc)RowNo, ISNULL(b.n,0) as n From [View_Statistics] a LEFT join (Select DISTINCT [组织编号] as col, COUNT(*) as n from [View_Statistics] Where [报修时间] between @beginDate and @endDate and [组织编号] in (@Param_0_0,@Param_0_1,@Param_0_2,@Param_0_3,@Param_0_4,@Param_0_5) group by [组织编号]) b on a.[组织编号] = b.col group by a.[组织编号],b.n) T;declare @sumNum int;Set @sumNum = (select SUM(n) from #temp_d74f87048c6a4f6b8572ccb2299d5ac7);Select col,n,Convert(decimal(18,2),CAST(n*100 AS FLOAT)/@sumNum) as ratio,Convert(decimal(18,2),CAST((select sum(n) from #temp_d74f87048c6a4f6b8572ccb2299d5ac7 where RowNo<=A.RowNo)*100 AS FLOAT) /@sumNum ) as total from #temp_d74f87048c6a4f6b8572ccb2299d5ac7 A group by RowNo,col,n order by n desc; if object_id(N''tempdb..#temp_d74f87048c6a4f6b8572ccb2299d5ac7'',N''U'') is not null drop table #temp_d74f87048c6a4f6b8572ccb2299d5ac7;',N'@Param_0_0 nvarchar(4),@Param_0_1 nvarchar(5),@Param_0_2 nvarchar(5),@Param_0_3 nvarchar(5),@Param_0_4 nvarchar(5),@Param_0_5 nvarchar(5),@beginDate datetime,@endDate datetime',@Param_0_0=N'DGMD',@Param_0_1=N'WXMD',@Param_0_2=N'HMMD',@Param_0_3=N'HYMD',@Param_0_4=N'HZMD',@Param_0_5=N'SDMD',@beginDate='2012-01-14 00:00:00',@endDate='2013-01-14 00:00:00'
重发一下exec sp_executesql N'if object_id(N''tempdb..#temp_d74f87048c6a4f6b8572ccb2299d5ac7'',N''U'') is not null drop table #temp_d74f87048c6a4f6b8572ccb2299d5ac7;Select * into #temp_d74f87048c6a4f6b8572ccb2299d5ac7 from (Select DISTINCT a.[组织编号] as col, row_number()over(order by b.n desc)RowNo, ISNULL(b.n,0) as n From [View_Statistics] a LEFT join (Select DISTINCT [组织编号] as col, COUNT(*) as n from [View_Statistics] Where [报修时间] between @beginDate and @endDate and [组织编号] in (@Param_0_0,@Param_0_1,@Param_0_2,@Param_0_3,@Param_0_4,@Param_0_5) group by [组织编号]) b on a.[组织编号] = b.col group by a.[组织编号],b.n) T;declare @sumNum int;Set @sumNum = (select SUM(n) from #temp_d74f87048c6a4f6b8572ccb2299d5ac7);Select col,n,Convert(decimal(18,2),CAST(n*100 AS FLOAT)/@sumNum) as ratio,Convert(decimal(18,2),CAST((select sum(n) from #temp_d74f87048c6a4f6b8572ccb2299d5ac7 where RowNo<=A.RowNo)*100 AS FLOAT) /@sumNum ) as total from #temp_d74f87048c6a4f6b8572ccb2299d5ac7 A group by RowNo,col,n order by n desc; if object_id(N''tempdb..#temp_d74f87048c6a4f6b8572ccb2299d5ac7'',N''U'') is not null drop table #temp_d74f87048c6a4f6b8572ccb2299d5ac7;',N'@Param_0_0 nvarchar(4),@Param_0_1 nvarchar(5),@Param_0_2 nvarchar(5),@Param_0_3 nvarchar(5),@Param_0_4 nvarchar(5),@Param_0_5 nvarchar(5),@beginDate datetime,@endDate datetime',@Param_0_0=N'DGMD',@Param_0_1=N'WXMD',@Param_0_2=N'HMMD',@Param_0_3=N'HYMD',@Param_0_4=N'HZMD',@Param_0_5=N'SDMD',@beginDate='2012-01-14 00:00:00',@endDate='2013-01-14 00:00:00'