今天在做数据库的存储过程的时候碰到了一个问题,我想对数据库中多天不同类别的数据量分别进行统计后一起传出来,刚开始我写的是以下Sql语句:
1 SELECT COUNT(*) as WaitWork from
2 SUDS_Work_Submit where File_Mark=0 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
3 SELECT COUNT(*) as FinishedWork from
4 SUDS_Work_Submit where File_Mark=3 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
5 SELECT COUNT(*) as WaitCheck from
6
7 SUDS_Work_Submit where File_Mark=1 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
8 SELECT COUNT(*) as BackWork from
9 SUDS_Work_Submit where File_Mark=2 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
10
11 END
这段代码在执行时并没有错误,但数据传出去的时候并不是以一张表的形式传出去的,而是以多张表的形式,这样就会造成无法将这些统计的多个数据量转换成DataTable表,从而会导致列名在数据表中无效,解决办法将这几个统计数据量组织成一张表的形式传出来。改进后的Sql语句如下:
1 select (SELECT COUNT(*) as WaitWork from
2 SUDS_Work_Submit where File_Mark=0 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as WaitWork,
3
4
5 (SELECT COUNT(*) as FinishedWork from
6 SUDS_Work_Submit where File_Mark=3 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as FinishedWork,
7
8
9 (SELECT COUNT(*) as WaitCheck from
10 SUDS_Work_Submit where File_Mark=1 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as WaitCheck,
11
12
13 (SELECT COUNT(*) as BackWork from
14 SUDS_Work_Submit where File_Mark=2 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as BackWork
15 END