如何在子查询中使用排序依据而不为该查询中使用的排序查询结果生成另一个表 [英] How Can I Use Order By In Subqueries Without Make Another Table For Result Of The Query That Order By Is Used In That Query

问题描述

我怎样才能从这个错误中用完为另外一个表来获得结果

选择ID，ISNULL（F01,0）+ ISNULL（F02,0）+ ISNULL（F03,0 ）作为RowSum来自dbo.Sheet1 $其中F01 = 1和F02 = 1订单由RowSum desc







这是我的查询

how can i run out from this error with out to make another table for result of
select ID, ISNULL(F01,0) + ISNULL(F02,0) + ISNULL(F03,0) as RowSum from dbo.Sheet1$ where F01 = 1 AND F02 = 1 order by RowSum desc



this is my query

update dbo.Sheet1$ set F01 = 0 where ID in(
select top 3 ID from dbo.Sheet1$ where ID in(
select ID, ISNULL(F01,0) + ISNULL(F02,0) + ISNULL(F03,0) as RowSum from dbo.Sheet1$ where F01 = 1 AND F02 = 1 order by RowSum desc))

这是错误



消息1033，等级15，状态1，行1

ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效，除非还指定了TOP，OFFSET或FOR XML。

and this is the error

Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

推荐答案

其中F01 = 1和F02 = 1个RowSum desc订单







这是我的查询

where F01 = 1 AND F02 = 1 order by RowSum desc



this is my query

update dbo.Sheet1

set F01 = 0 其中 ID （
选择 top 3 ID 来自 dbo.Sheet1

set F01 = 0 where ID in( select top 3 ID from dbo.Sheet1

其中 ID （
选择 ID，ISNULL（F01， 0 ）+ ISNULL（F02， 0 ）+ ISNULL（F03， 0 ） as RowSum 来自 dbo.Sheet1

where ID in( select ID, ISNULL(F01,0) + ISNULL(F02,0) + ISNULL(F03,0) as RowSum from dbo.Sheet1

这篇关于如何在子查询中使用排序依据而不为该查询中使用的排序查询结果生成另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！