使用sql server System.Data.SqlClient.SqlCommand,为何Order By不起任何作用

[erictang2003]

在网上查的写法

SELECT * FROM ( SELECT TOP (100) PERCENT * FROM myTable ORDER BY myTable.uid DESC) as newtable

能够返回结果，但是ORDER BY是不起任何作用的,无论是 ASC 还是 DECS，

而且如果直接写SELECT * FROM myTable ORDER BY myTable.uid DESC 赋值给System.Data.SqlClient.SqlCommand.CommandText,
会报错SQLException: Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

烦死了，急求解答

[dalmeeme]

SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by myTable.uid DESC

[erictang2003]

一样报错,Column "newTable.uid" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
疯掉~~


[Quote=引用 1 楼 dalmeeme 的回复:]

SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by myTable.uid DESC
[/Quote]

[erictang2003]

这样写也能返回结果但是ORDER BY 同样无效,晕~~

SELECT * FROM myTable WHERE guid IN ( SELECT TOP (100) PERCENT guid FROM myTable ORDER BY myTable.uid DESC)

[dalmeeme]

newTable.uid有这个字段不

[dalmeeme]

在sql server的查询编辑器里好像没错哦。

[erictang2003]

有哇，我快疯了，这是很简单的查询，在ACCESS和MYSQL 都没问题的

[Quote=引用 4 楼 dalmeeme 的回复:]

newTable.uid有这个字段不
[/Quote]

[只记今朝笑]

SQL文写法没有问题。看看你的myTable.uid数据类型

[erictang2003]

是字符串，一样可以按ABCD1234排序的

[Quote=引用 7 楼 jli033 的回复:]

SQL文写法没有问题。看看你的myTable.uid数据类型
[/Quote]

[kaikai_kk]

因为你用了TOP (100) PERCENT,这是返回所有数据，外查询还是根据你的主键或者默认数据来显示的
可以把order by放在外面,或者外面再用一次order by 或者改Top n (n<100)


SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by myTable.uid DESC
这个错误的原因是外查询表名并不是myTable了，当然不存在myTable.uid

[kaikai_kk]

SELECT * FROM ( SELECT TOP (100) PERCENT * FROM myTable ORDER BY myTable.uid DESC) as newtable

即等效於
SELECT * FROM myTable

[erictang2003]

SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by newtable.uid DESC 一样抛出异常SQLException: Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


[Quote=引用 9 楼 kaikai_kk 的回复:]

因为你用了TOP (100) PERCENT,这是返回所有数据，外查询还是根据你的主键或者默认数据来显示的
可以把order by放在外面,或者外面再用一次order by 或者改Top n (n<100)


SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as new……
[/Quote]

[kaikai_kk]

看我回复红色标的字，外查询表名已经不是myTable了，所以不能用myTable.uid，直接用uid

[erictang2003]

SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by uid DESC 一样抛出异常SQLException: Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


[Quote=引用 12 楼 kaikai_kk 的回复:]

看我回复红色标的字，外查询表名已经不是myTable了，所以不能用myTable.uid，直接用uid
[/Quote]

[kaikai_kk]

SELECT * FROM ( SELECT TOP (100) percent * FROM myTable ORDER BY myTable.uid DESC) as newtable order by uid DESC

只要存在myTable表，存在uid列，这语句就没问题

你这个异常是你用的统计的函数，比如min, max, sum ...,而但某个字段不在统计中，也不要Group by中
你确定语句是这样写的？

[c02645]

SELECT * FROM myTable ORDER BY uid DESC 这样

[erictang2003]

经排查，原因是因为在此查询后又直接拿这个有ORDER BY的SQL语句执行了一次 ExecuteScalar(),导致出现以上异常，因此代码是从ACCESS直接移植过来的，ACCESS上可以通过但SQL SERVER不可以在ExecuteScalar(）上执行OREDER BY !