sql - 如何按不同表中三列的总和排序？

Question

select username,
(select COUNT(OpenUser) from TIcket t with(nolock) where t.OpenUser = u.UserName and cast(t.CompletedDate as date) = cast(getdate() as date) and t.IssueClass = 58) as ServiceNote,
(select COUNT(AssignUser) from TIcket t with(nolock) where ((t.AssignUser = u.UserName and t.Status = 1) and t.IssueClass != 58)) as status1,
(select COUNT(PickUpUser) from TIcket t with(nolock) where ((t.PickUpUser = u.UserName and t.Status = 2) and t.IssueClass != 58)) as status2
  from users u
  where isinactive = 0
  and UserLevel > -1 and First_Name != '' 
  and center_id = '100'

我查询了我的程序。我想按三个字段 ServiceNote、status1、status2 的总和进行排序。我尝试使用“Sum”进行订购，（例如按 (ServiceNote,status1,status2) 订购，但它不起作用。

标签： sqlsql-server

Answer 1

这不行吗？

order by (serviceNote + status1 + status2)

这适用于大多数数据库，但不适用于 SQL Server。为此，请使用 CTE 或子查询：

with cte as (
      <your query here>
     )
select cte.*
from cte
order by (serviceNote + status1 + status2);