英文:
How can I order by sum of three column from different tables?
问题
Sure, here's the translated query you provided:
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';
If you want to order by the sum of the three fields (ServiceNote, status1, and status2), you can use the following code:
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'
order by (ServiceNote + status1 + status2);
This will order the results based on the sum of the ServiceNote, status1, and status2 columns.
英文:
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'
I made query for my program. And I want to order by sum of three field ServiceNote,status1,status2.
I tried to order using 'Sum',(like order by (ServiceNote,status1,status2) but it doesn't work.
答案1
得分: 1
这在大多数数据库中有效，但在SQL Server中无效。为此，请使用公用表达式（CTE）或子查询：
with cte as (
<在此处输入您的查询>
)
select cte.*
from cte
order by (serviceNote + status1 + status2);
英文:
Doesn't this work?
order by (serviceNote + status1 + status2)
This works in most databases, but not in SQL Server. For that, use a CTE or subquery:
with cte as (
<your query here>
)
select cte.*
from cte
order by (serviceNote + status1 + status2);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论