列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中
我有下面的sql查询,但执行它时遇到问题。列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中
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
但我不断收到以下错误:
Column 'FloorId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
当你使用aggregrates(如最大)只能使用是一组的部分领域BY子句。
因此,如果你想要其他字段将它们添加到group by子句中,请删除'*'。
- 你有MAX这是聚集所以你需要GROUP BY编号
- ...这将不会再工作,因为你有ROW_NUMBER
- 你真的想要一个笛卡尔乘积(CROSS JOIN )在地板和用户之间?
- 什么列属于哪个表?
或许,这可以帮助你得到你想要的:
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
我想'WHERE id = userid'是JOIN标准。 – 2011-02-25 06:09:14
Id是楼层用户标识。我错过了输入,应该是'floorusrId' – 2011-02-25 06:15:47
SELECT * from(选择row_number()OVER(Order By FloorUserId)作为'row_number',FloorUserId, max(CASE WHEN AreaId ='[G]'或AreaId = N' 'L01', max(CASE WHERE AreaId ='[G]'或AreaId = N'L02'THEN'X'ELSE''END)为'L02' floor,tbuser,其中Floor.FloorUserId = tbuser.userID )as derivedTable where row_number BETWEEN 1 AND 20 – 2011-02-25 06:51:39
你显示整个查询? – mellamokb 2011-02-25 05:57:38