SELECT TOP 10 * FROM ( select SUM(o.PayAmount) as SumPayAmount, SUM(o.OrderMoney) as SumOrderMoney, ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, oid.OrderNo,o.CreateMainUser,su1.ContactPerson,IsNULL((su2.No+'-'+su2.Name),'') as CmUser,su1.Name,IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,o.CreateTime,o.OrderMoney,o.PayAmount from Platform_OrderIdInfo oid with(nolock) inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id where BaoyUserId>0 and o.OrderStatus=100 ) AS t WHERE t.rownumber > 0;

这是一个 SQL 查询语句，它的作用是从数据库中查询出符合条件的数据，并按照 CreateTime 字段进行排序，最终返回前 10 条数据。其中，查询的表是 Platform_OrderIdInfo、Platform_InsOrders、Sys_User 等表，它们之间通过一些字段进行了关联。同时，查询结果中包含了一些字段的汇总值，如 SumPayAmount 和 SumOrderMoney。

相关问题

declare @startTime date declare @endTime date declare @Uid int SELECT TOP 10 * FROM ( select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, oid.OrderNo,o.CreateMainUser,su1.ContactPerson,IsNULL((su2.No+'-'+su2.Name),'') as CmUser,su1.Name,IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,o.CreateTime,o.OrderMoney,o.PayAmount from Platform_OrderIdInfo oid with(nolock) inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id where BaoyUserId>0 and o.OrderStatus=100 and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL) order by o.CancelTime ) AS t WHERE t.rownumber > 0;错误除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

错误信息提示 ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，这是因为在子查询中使用了 ORDER BY 语句，但是子查询是没有排序概念的，所以会报错。

解决方法一般有两种：

1. 去掉子查询中的 ORDER BY 语句，将 TOP 10 放到子查询外面。

2. 将子查询转换为一个临时表或表变量，然后在临时表或表变量上使用 ORDER BY 语句。

第一种解决方法的示例代码如下：

declare @startTime date 
declare @endTime date 
declare @Uid int 

SELECT TOP 10 * FROM (
    select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, 
           oid.OrderNo,
           o.CreateMainUser,
           su1.ContactPerson,
           IsNULL((su2.No+'-'+su2.Name),'') as CmUser,
           su1.Name,
           IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,
           o.CreateTime,
           o.OrderMoney,
           o.PayAmount 
    from Platform_OrderIdInfo oid with(nolock) 
    inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo 
    inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id 
    inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id 
    inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id 
    where BaoyUserId>0 and o.OrderStatus=100 
          and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) 
          and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL)
) AS t 
WHERE t.rownumber > 0
ORDER BY t.CreateTime;

在上述代码中，我们将 TOP 10 放到子查询外面，然后在最后使用 ORDER BY 语句对结果进行排序，这样就可以避免错误了。

第二种解决方法的示例代码如下：

declare @startTime date 
declare @endTime date 
declare @Uid int 

DECLARE @tempTable TABLE (
    rownumber INT,
    OrderNo VARCHAR(50),
    CreateMainUser INT,
    ContactPerson VARCHAR(50),
    CmUser VARCHAR(100),
    Name VARCHAR(50),
    OpreaCmUser VARCHAR(100),
    CreateTime DATETIME,
    OrderMoney MONEY,
    PayAmount MONEY
)

INSERT INTO @tempTable
select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, 
       oid.OrderNo,
       o.CreateMainUser,
       su1.ContactPerson,
       IsNULL((su2.No+'-'+su2.Name),'') as CmUser,
       su1.Name,
       IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,
       o.CreateTime,
       o.OrderMoney,
       o.PayAmount 
from Platform_OrderIdInfo oid with(nolock) 
inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo 
inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id 
inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id 
inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id 
where BaoyUserId>0 and o.OrderStatus=100 
      and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) 
      and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL)

SELECT TOP 10 * FROM @tempTable WHERE rownumber > 0 ORDER BY CreateTime;

在上述代码中，我们使用了一个表变量 @tempTable，将子查询的结果插入到该表变量中，然后在表变量上使用 ORDER BY 语句对结果进行排序并取出 TOP 10 数据。

public class PayAmountContext { // 依赖倒置原则，面向接口编程 // 客户端需要设置自己想要使用的具体策略类，因此需要客户端对策略类有具体的了解，这点也是策略设计模式的不足之处 private PayAmountStrategy payAmountStrategy; public PayAmountContext(/*PayAmount payAmount*/) { /*this.payAmount = payAmount;*/ } public void setPayAmount(PayAmountStrategy payAmountStrategy) { this.payAmountStrategy = payAmountStrategy; } public double payAmount(Object param) { return this.payAmountStrategy.payAmount(param); } }

这段代码实现了策略模式，通过将具体算法实现封装到不同的策略类中，使得客户端可以根据需要动态地选择不同的策略类。其中，PayAmountContext类扮演了上下文角色，负责与客户端交互，维护一个对PayAmountStrategy接口的引用，以便于动态地设置具体的策略类。当客户端需要调用具体的算法时，只需要调用PayAmountContext的payAmount方法即可，该方法内部会调用当前设置的PayAmountStrategy的payAmount方法来计算费用。这样，当需要增加新的算法时，只需要实现PayAmountStrategy接口，并在客户端动态设置即可，无需修改原有代码，符合开闭原则。但是，该模式的缺点是客户端需要对策略类有具体的了解，如果策略类过多或者复杂，客户端的维护成本会增加。