SQL 服务器存储过程帮助执行以下查询

Question

create table testjob  
(  
jobid int,  
jobname varchar(100),  
time float,  
name varchar(50),  
Date varchar(100),  
comments varchar(500)  
)
insert into testjob values ( 1001,'java work',4.5,'arjun','9/26/2012  12:00:00 AM','Sample test comments 1')
insert into testjob values ( 1005,'sql work',10,'arjun','9/28/2012  12:00:00 AM','Sample test comments 2')
insert into testjob values ( 1010,'.net work',7.5,'arjun','8/13/2012  12:00:00 AM','Sample test comments 3')
insert into testjob values ( 1040,'java work',5.5,'ravi','9/14/2012  12:00:00 AM','Sample test comments 1')
insert into testjob values ( 1023,'php work',2.5,'arjun','9/5/2012  12:00:00 AM','Sample test comments 4')
insert into testjob values ( 1027,'.net work',3.5,'ravi','8/24/2012  12:00:00 AM','Sample test comments 2')

我想要一个不使用游标的过程，以便我的 ouptut 如下所示：（如果可能的话，我希望查询使用 with 运算符）

Name:Arjun(24.5 Hrs spent)
jobname          Time            Date                Comments  
java work     4.5     9/26/2012  12:00:00 AM   Sample test comments 1   
sql work      10      9/28/2012  12:00:00 AM   Sample test comments 2  
.net work     7.5     8/13/2012  12:00:00 AM   Sample test comments 3  
php  work     2.5     9/5/2012  12:00:00 AM    Sample test comments 4
Name:Ravi(9 Hrs spent)
jobname       time           Date             Comments  
java work     5.5     9/14/2012  12:00:00 AM   Sample test comments 1  
.net work     3.5     8/24/2012  12:00:00 AM   Sample test comments 2  

Answer 1

看看这个SQL小提琴

无需对查询使用 WITH。

select name,case when jobname is null
                 then cast(sum(time) as varchar(12)) + ' hrs spent'
                 else jobname end jobname,time,date,comments
from testjob
group by grouping sets ((name),(name,jobname,time,date,comments))
order by name,comments

您实际上不需要分组集，我只是为了向您展示如何在查询工具中使用汇总标题查看它。 如果要将此数据带到像Visual Studio这样的前端工具，则应控制那里的分组，并简单地以排序的方式返回数据，例如

select name,jobname,time,date,comments
from testjob
order by name,comments
You keep a running total of time until the name changes.

仅供参考，您确实应该选择更好的排序顺序，例如"日期"而不是"评论"，但这是我发现的唯一可以准确给出您显示的结果的顺序。