TSQL获取记录计数和单个查询中的记录

Question

我得到了这个包含 TODO 项目的任务表。我们正在单个存储过程中使用单独的查询来检索待办事项和已完成、待处理任务的计数，即使它是从同一个表进行查询。这是查询，

select
TaskName 'Task/TaskName',
CASE IsDone WHEN '1' THEN 'True' ELSE 'False' END 'Task/IsDone',
(
 SELECT COUNT(*) FROM Tasks WHERE IsDone = '1'
) 'CompletedCount'
FROM Tasks FOR XML PATH('Tasks')

这是输出

'<Tasks>
    <Task>
        <TaskName>Write a email to Mayor<TaskName>
        <IsDone>True</IsDone>
        <CompletedCount>2<CompletedCount>
    </Task>
</Tasks>'

CompletedCount 存在于每个任务中，这是不必要的，而且无论如何我也可以查询计数，而无需显式编写此 SELECT COUNT(*) FROM Tasks WHERE IsDone = '1'

我如何获得如下输出

'<Tasks>
    <CompletedCount>2<CompletedCount>
    <Task>
        <TaskName>Write a email to Mayor<TaskName>
        <IsDone>True</IsDone>
    </Task>
    <Task>
        <TaskName>Organize Campaign for website<TaskName>
        <IsDone>False</IsDone>
    </Task>
</Tasks>'

Answer 1

select (
          select count(*)
          from Tasks
          where IsDone = 1
          for xml path('CompletedCount'), type
       ),
       (
          select TaskName,
                 case IsDone 
                   when 1 then 'True' 
                   else 'False' 
                 end as IsDone
          from Tasks
          for xml path('Task'), type
       )
for xml path('Tasks')

更新：
如果您首先构建任务列表，然后查询 XML 以获取已完成的计数，则可以通过单一选择来完成此操作。我怀疑这会比使用两个 select 语句更快。

;with C(Tasks) as
(
  select TaskName,
         case IsDone 
           when 1 then 'True' 
           else 'False' 
         end as IsDone
  from Tasks
  for xml path('Task'), type
)
select C.Tasks.value('count(/Task[IsDone = "True"])', 'int') as CompletedCount,
       C.Tasks
from C
for xml path('Tasks')

Answer 2

您可以使用 type 来计算子查询中的 XML 部分：

declare @todo table (TaskName varchar(50), IsDone bit)
insert @todo values ('Buy milk',1)
insert @todo values ('Send thank you note',1)

select  sum(case when isdone = 1 then 1 end) as 'CompletedCount'
,       (
        select  TaskName 'TaskName'
        ,       case when isdone = 1 then 'True' else 'False' end 'IsDone'
        from    @todo
        for xml path('Task'), type
        ) as 'TaskList'
from    @todo
for xml path('Tasks')

这将打印：

<Tasks>
    <CompletedCount>2</CompletedCount>
    <TaskList>
        <Task>
            <TaskName>Buy milk</TaskName>
            <IsDone>True</IsDone>
        </Task>
        <Task>
            <TaskName>Send thank you note</TaskName>
            <IsDone>True</IsDone>
        </Task>
    </TaskList>
</Tasks>