数据库中多条不同数据的统计时组织成一张表

今天在做数据库的存储过程的时候碰到了一个问题，我想对数据库中多天不同类别的数据量分别进行统计后一起传出来，刚开始我写的是以下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

转载于:https://www.cnblogs.com/xiaopanlyu/archive/2012/02/23/2365264.html