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 子句中。
时间: 2024-03-27 09:40:11 浏览: 16
根据报错信息,提示选择列表中的列 '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 子句中。