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

最新推荐文章于 2024-08-19 09:59:54 发布

ITloveBaby

最新推荐文章于 2024-08-19 09:59:54 发布

阅读量721

点赞数

CC 4.0 BY-SA版权

分类专栏： Sql语句的组织优化文章标签：数据库 file sql 存储

本文链接：https://blog.youkuaiyun.com/ITloveBaby/article/details/7259239

Sql语句的组织优化专栏收录该内容

1 篇文章

订阅专栏

在编写存储过程时遇到问题，需要统计数据库中不同File_Mark的数据量并统一输出。原始SQL语句导致结果分散在多张表中，无法直接转化为DataTable。解决方案是将统计查询整合到同一张表中，确保所有计数结果在同一结构下返回。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在做数据库的存储过程的时候碰到了一个问题，我想对数据库中多天不同类别的数据量分别进行统计后一起传出来，刚开始我写的是以下Sql语句：
SELECT COUNT(*) as WaitWork from
SUDS_Work_Submit where File_Mark=0 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
SELECT COUNT(*) as FinishedWork from
SUDS_Work_Submit where File_Mark=3 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)

SELECT COUNT(*) as WaitCheck from

SUDS_Work_Submit where File_Mark=1 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)
SELECT COUNT(*) as BackWork from

SUDS_Work_Submit where File_Mark=2 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)

END

这段代码在执行时并没有错误，但数据传出去的时候并不是以一张表的形式传出去的，而是以多张表的形式，这样就会造成无法将这些统计的多个数据量转换成DataTable表，从而会导致列名在数据表中无效，解决办法将这几个统计数据量组织成一张表的形式传出来。改进后的Sql语句如下：

select (SELECT COUNT(*) as WaitWork from

SUDS_Work_Submit where File_Mark=0 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as WaitWork,

(SELECT COUNT(*) as FinishedWork from
SUDS_Work_Submit where File_Mark=3 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as FinishedWork,

(SELECT COUNT(*) as WaitCheck from
SUDS_Work_Submit where File_Mark=1 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as WaitCheck,

(SELECT COUNT(*) as BackWork from
SUDS_Work_Submit where File_Mark=2 and Staff_ID=(select UserID from Accounts_Users where UserName=@StaffName)) as BackWork
END