select top 10 sum(gold) as goldcount,sum(lottery) as lotterycount,gold,lottery
from RecommenderLog2 where 0=0 and Convert(varchar(100),checkOutDate,23)= '2010-08-20'
group by goldcount,lotterycount order by CheckOutDate desc服务器: 消息 207,级别 16,状态 3,行 3
列名 'goldcount' 无效。
服务器: 消息 207,级别 16,状态 1,行 3
列名 'lotterycount' 无效。
服务器: 消息 207,级别 16,状态 1,行 3
列名 'goldcount' 无效。
服务器: 消息 207,级别 16,状态 1,行 3
列名 'lotterycount' 无效。
请问如何解决?急
如 goldcount改成sum(gold)
SUM(lottery) AS lotterycount,
gold,
lottery
FROM RecommenderLog2
WHERE 0 = 0
AND CONVERT(VARCHAR(100), checkOutDate, 23) = '2010-08-20'
GROUP BY
goldcount,
lotterycount
ORDER BY
CheckOutDate DESC
GROUP BY
gold,
lottery
group by gold,lottery
在关键字 'GROUP' 附近有语法错误。
from RecommenderLog2 where 0=0 and Convert(varchar(100),checkOutDate,23)= '2010-08-20'
group by gold,lottery --order by CheckOutDate desc
top 10
sum(gold) as goldcount,
sum(lottery) as lotterycount,
gold,
lottery
from
RecommenderLog2
where
0=0 and Convert(varchar(10),checkOutDate,120)= '2010-08-20'
group by
gold,lottery
order by
max(CheckOutDate) desc
order by 中的列名不能用列别名
SUM(lottery) AS lotterycount,
gold,
lottery
FROM RecommenderLog2
WHERE 0 = 0
AND CONVERT(VARCHAR(100), checkOutDate, 23) = '2010-08-20'
GROUP BY
goldcount,
lotterycount
ORDER BY
CheckOutDate DESC
GROUP BY
gold,
lottery
from RecommenderLog2 where 0=0 and Convert(varchar(100),checkOutDate,23)= '2010-08-20'
group by goldcount,lotterycount order by CheckOutDate desc
语句逻辑有问题,select中有 gold 还有 sum(gold),有lottery 还有sum(lottery) 。
这样是不行的
from RecommenderLog2 where 0=0 and Convert(varchar(100),checkOutDate,23)= '2010-08-20'
group by gold,lottery order by CheckOutDate desc
select top 10 sum(gold) as goldcount,sum(lottery) as lotterycount,gold,lottery
from RecommenderLog2 where 0=0 and Convert(varchar(100),checkOutDate,23)= '2010-08-20'
group by goldcount,lotterycount order by CheckOutDate desc語法沒問題
分组的意义是什么?将分组列表中的字段的值相同的记录分在一组,最终只返回一行记录。所以SELECT列表中不在分组字段列表中的字段必须加聚合函数进行聚合,否则最后无法确定返回哪一行的值。如果对不再分组列表中的字段不加聚合函数,SQL SERVER 2005 必定会报出语法错误。
更何况分组子句中不能使用SELECT列表中为字段定义的别名,因为SQL SERVER 先处理分组语句,然后才处理SELECT 列表。
SQL SERVER 逻辑查询处理步骤如下:
1、执行笛卡尔积
2、应用ON筛选器
3、添加外部行
4、分组
5、应用CUBE或ROLLUP 选项 (2005以后)
6、应用HAVING筛选器
7、处理SELECT 列表
8、应用DISTINCT 子句
9、应用ORDER BY 子句
10、应用TOP选项所以ORDER BY 可以使用列别名,而 GROUP BY 不行