I have sql query below but i face a problem when execute it.
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 BE开发者_高级运维TWEEN 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.
- You have MAX which is for aggregates so you'd need GROUP BY Id
- ...this won't then work because you have ROW_NUMBER
- Do you really want a Cartesian product (CROSS JOIN) between floor and user?
- 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
You can only use fields that are part of a group by clause when you use aggregrates (like max).
So get rid of the '*' if you want other fields add them into a group by clause.
精彩评论