order by 1 与 order by 列名

最新推荐文章于 2022-05-09 17:19:07 发布

苦苦的潜行者

最新推荐文章于 2022-05-09 17:19:07 发布

阅读量7.9k

点赞数 2

分类专栏： SQL/数据库文章标签： sql order by

本文链接：https://blog.csdn.net/xiaolinyouni/article/details/53571796

版权

SQL/数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

经常见sql语句中order by 1或者order by 2...order by N,有时候很莫名其妙.其实1表示第一个栏位,2表示第二栏位; 依此类推,当表中只有2个栏位时,oder by 3就会出错,这个跟order by 列名没有什么区别,不过在特殊情况下还是很有用的.

例如table1(p_code int,issue_date datetime,issue_num int)

p_code issue_date issue_num
101   2016-12-01 00:00:00.000 45
102   2016-12-01 00:00:00.000 89
102    2016-12-03 00:00:00.000 44
103    2016-12-03 00:00:00.000 44
101    2016-12-02 00:00:00.000 44
101    2016-12-03 00:00:00.000 44
101    2016-12-03 00:00:00.000 45
101    2016-12-03 00:00:00.000 44
101    2016-12-03 00:00:00.000 44
102    2016-12-03 00:00:00.000 47
101    2016-12-03 00:00:00.000 48
104    2016-12-03 00:00:00.000 86
101    2016-12-03 00:00:00.000 56
101    2016-12-03 00:00:00.000 29
101    2016-12-11 00:00:00.000 11

我想获取某个p_code过去(不含当天)10次的issue_num平均值,

可能我们会这么写

select top 10 p_code, avg(issue_num) as avgissue from table1 
where issue_date<>(select convert(varchar(10),getdate(),120))
and p_code='101'
group by p_code
order by issue_date desc

但是以上写法是错误,的,会提示 Column "table1.issue_date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.必须要求issue_date在group by中.

所以我们改成

select top 10 p_code, avg(issue_num) as avgissue from table1 
where issue_date<>(select convert(varchar(10),getdate(),120))
and p_code='101'
group by p_code,issue_date
order by issue_date desc

但是得到的结果却是(今天是2016.12.11)

p_code avgissue
101 44
101 44
101 45

显然以上不是我们想要的结果.

那么该如何结果这种问题呢,当然你可以再次进行avg()聚合运算,但是这里我们既然说oder by N,那么就用这个知识来解决.

语句如下.

select top 10 p_code, avg(issue_num) as avgissue from table1 
where issue_date<>(select convert(varchar(10),getdate(),120))
and p_code='101'
group by p_code
order by 2 desc

结果如下(今天是2016.12.11)

p_code avgissue
101 44

是不是很神奇呢!

当然作为码农,我们尽量用列名来解决,以便增加代码的可读性和易维护性.