列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中

时间:2022-07-01 22:56:26

I have sql query below but i face a problem when execute it.

我有下面的SQL查询,但我执行它时遇到问题。

SELECT * from (Select row_number() OVER(Order By FloorUserId) as 'row_number', FloorUserId,
max(CASE WHEN AreaId='[G]' or AreaId=N'L01'  THEN 'X' ELSE ' ' END) as 'L01',
max(CASE WHEN AreaId='[G]' or AreaId=N'L02'  THEN 'X' ELSE ' ' END) as 'L02'
from floor, tbuser where FloorUserId= tbuser.userID  
    )  as derivedTable where row_number BETWEEN 1 AND 20

But I keep getting the following error:

但我不断收到以下错误:

Column 'FloorId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列'FloorId'在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。

2 个解决方案

#1


3  

  • You have MAX which is for aggregates so you'd need GROUP BY Id
  • 你有MAX用于聚合,所以你需要GROUP BY Id
  • ...this won't then work because you have ROW_NUMBER
  • ...这不会起作用,因为你有ROW_NUMBER
  • Do you really want a Cartesian product (CROSS JOIN) between floor and user?
  • 你真的想在地板和用户之间使用笛卡尔积(CROSS JOIN)吗?
  • what column belongs to what table?
  • 什么列属于什么表?

Perhaps this may help you to get where you want:

也许这可以帮助你到达你想要的地方:

Select
     row_number() OVER (PARTITION BY userid Order By user.Id) as 'row_number', user.Id,
     max(CASE WHEN  floor.AreaId='[G]' or  floor.AreaId=N'L01'  THEN 'X' ELSE ' ' END) as 'L01',
     max(CASE WHEN floor. AreaId='[G]' or  floor.AreaId=N'L02'  THEN 'X' ELSE ' ' END) as 'L02'
from
    floor
    JOIN
    user ON floor. = user.    --what?
where
    user.Id = userID 
group by
    user.Id

#2


0  

You can only use fields that are part of a group by clause when you use aggregrates (like max).

使用聚合(如max)时,只能使用属于group by子句的字段。

So get rid of the '*' if you want other fields add them into a group by clause.

因此,如果您希望其他字段将它们添加到group by子句中,请删除“*”。

#1


3  

  • You have MAX which is for aggregates so you'd need GROUP BY Id
  • 你有MAX用于聚合,所以你需要GROUP BY Id
  • ...this won't then work because you have ROW_NUMBER
  • ...这不会起作用,因为你有ROW_NUMBER
  • Do you really want a Cartesian product (CROSS JOIN) between floor and user?
  • 你真的想在地板和用户之间使用笛卡尔积(CROSS JOIN)吗?
  • what column belongs to what table?
  • 什么列属于什么表?

Perhaps this may help you to get where you want:

也许这可以帮助你到达你想要的地方:

Select
     row_number() OVER (PARTITION BY userid Order By user.Id) as 'row_number', user.Id,
     max(CASE WHEN  floor.AreaId='[G]' or  floor.AreaId=N'L01'  THEN 'X' ELSE ' ' END) as 'L01',
     max(CASE WHEN floor. AreaId='[G]' or  floor.AreaId=N'L02'  THEN 'X' ELSE ' ' END) as 'L02'
from
    floor
    JOIN
    user ON floor. = user.    --what?
where
    user.Id = userID 
group by
    user.Id

#2


0  

You can only use fields that are part of a group by clause when you use aggregrates (like max).

使用聚合(如max)时,只能使用属于group by子句的字段。

So get rid of the '*' if you want other fields add them into a group by clause.

因此,如果您希望其他字段将它们添加到group by子句中,请删除“*”。