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;
时间: 2024-03-28 22:37:41 浏览: 12
这是一个 SQL 查询语句,它的作用是从数据库中查询出符合条件的数据,并按照 CreateTime 字段进行排序,最终返回前 10 条数据。其中,查询的表是 Platform_OrderIdInfo、Platform_InsOrders、Sys_User 等表,它们之间通过一些字段进行了关联。同时,查询结果中包含了一些字段的汇总值,如 SumPayAmount 和 SumOrderMoney。
相关问题
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;提示选择列表中的列 'Platform_InsOrders.CreateTime' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
根据报错信息,提示选择列表中的列 'Platform_InsOrders.CreateTime' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。这是因为在 SQL 中,如果 SELECT 语句中包含了聚合函数,如 SUM、AVG、COUNT 等,那么 SELECT 语句中的非聚合列必须要包含在 GROUP BY 子句中,否则会报错。在这个 SQL 语句中,SUM 语句对 PayAmount 和 OrderMoney 进行了求和,但是 CreateTime 列没有被聚合,因此需要在该 SQL 语句中添加 GROUP BY 子句。
可以将 SQL 语句修改为以下形式:
```
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
GROUP BY
oid.OrderNo,
o.CreateMainUser,
su1.ContactPerson,
su2.No,
su2.Name,
su1.Name,
su3.No,
su3.Name,
o.CreateTime,
o.OrderMoney,
o.PayAmount
) AS t
WHERE
t.rownumber > 0;
```
在 SQL 语句中添加了 GROUP BY 子句,并将列名添加到了 GROUP 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) 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 数据。