精华内容
下载资源
问答
  • mysql降序与升序

    千次阅读 2019-03-13 10:51:17
    通过 ”order by“实现,语法 :order by 字段 asc/desc。 sql:select * from tablename order by filename1 desc; 解释:上面语句的意思就是根据”filename1字段“排序,...备注:asc是表示升序,desc表示降序。 ...
    通过 ”order by“实现,语法 :order by  字段  asc/desc。
    sql:select *  from tablename  order by filename1 desc;
    解释:上面语句的意思就是根据”filename1字段“排序,倒叙输出tablename表中的数据。
    备注:asc是表示升序,desc表示降序。
    展开全文
  • MYSQL降序排列

    万次阅读 2009-04-15 10:45:00
    mysql> SELECT artist_id , caption FROM artist_lang ORDER BY artist_id desc;

     mysql> SELECT artist_id , caption FROM artist_lang ORDER BY artist_id desc;

    展开全文
  • MYSQL数据库,一个字段存的格式如"18,2","13,2","10,1",评分的功能,对应的总分为18/2,13/2,10/1,请问用什么查询语句查询出相除后的结果以降序排列。
  • 学生表 成绩表 实现代码: SELECT a1.student_id '学号', d.student_name '姓名', a1.score 'Java基础', a2.score 'Java高级', a3.score '前端', b.sumscore '总成绩', ... ( SELECT ss.student_id, ss.score ...

    学生表

    在这里插入图片描述

    成绩表

    在这里插入图片描述

    实现代码:

    SELECT
    	a1.student_id '学号',
    	d.student_name '姓名',
    	a1.score 'Java基础',
    	a2.score 'Java高级',
    	a3.score '前端',
    	b.sumscore '总成绩',
    	c.avgscore '平均分' 
    FROM
    	( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 1 ) a1
    	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 2 ) a2 ON a1.student_id = a2.student_id
    	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 3 ) a3 ON a3.student_id = a2.student_id
    	LEFT JOIN ( SELECT ss.student_id, sum( ss.score ) sumscore FROM student_socre ss GROUP BY ss.student_id ) b ON a3.student_id = b.student_id
    	LEFT JOIN ( SELECT ss.student_id,CAST(AVG( score ) AS DECIMAL ( 10, 2 )) avgscore FROM student_socre ss 
    	GROUP BY ss.student_id) c ON c.student_id = b.student_id
    	LEFT JOIN ( SELECT s.student_id, s.student_name FROM student s GROUP BY s.student_id ) d ON c.student_id = d.student_id 
    ORDER BY b.sumscore DESC
    

    效果:

    在这里插入图片描述
    只显示个别班级的学生(例子显示一班的学生)

    实现代码

    SELECT
    	a1.student_id '学号',
    	d.student_name '姓名',
    	a1.score 'Java基础',
    	a2.score 'Java高级',
    	a3.score '前端',
    	b.sumscore '总成绩',
    	c.avgscore '平均分'	
    FROM
    ( SELECT s.student_id, s.student_name FROM student s WHERE class_id=1 GROUP BY s.student_id ) d 
    	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 1 ) a1 ON a1.student_id = d.student_id
    	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 2 ) a2 ON a1.student_id = a2.student_id
    	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 3 ) a3 ON a3.student_id = a2.student_id
    	LEFT JOIN ( SELECT ss.student_id, sum( ss.score ) sumscore FROM student_socre ss GROUP BY ss.student_id ) b ON a3.student_id = b.student_id
    	LEFT JOIN ( SELECT ss.student_id,CAST(AVG( score ) AS DECIMAL ( 10, 2 )) avgscore FROM student_socre ss 
    	GROUP BY ss.student_id) c ON c.student_id = b.student_id
    ORDER BY b.sumscore DESC
    

    效果:

    在这里插入图片描述

    若不排序 简化版

    实现代码

    SELECT
    	s.student_id AS 学号,
    	s.student_name AS 姓名,
    	MAX( CASE WHEN ss.course_id = 1 THEN score ELSE 0 END ) AS Java基础,
    	MAX( CASE WHEN ss.course_id = 2 THEN score ELSE 0 END ) AS Java高级,
    	MAX( CASE WHEN ss.course_id = 3 THEN score ELSE 0 END ) AS 前端,
    	CAST(
    	AVG( score ) AS DECIMAL ( 10, 2 )) AS 平均分,
    	SUM( score ) AS 总分 
    FROM student s
    	LEFT JOIN student_socre ss ON s.student_id = ss.student_id 
    GROUP BY s.student_id
    

    效果:

    在这里插入图片描述

    展开全文
  • MYSQL 降序索引和减轻索引扫描

    千次阅读 2006-12-22 10:57:00
    var src_url=http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan;原文作者: Peter Zaitsev原文来源: http://www.mysqlperformanceblog.com/2006/05/09/descending-indexin

    <script language="javascript" type="text/javascript">var src_url='http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan';</script>
    <script language="javascript" src="misc/peter_cpr.js" type="text/javascript"></script>

    原文作者: Peter Zaitsev
    原文来源: http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan
    译者:叶金荣(Email:),转载请注明译者和出处,并且不能用于商业用途,违者必究。

    Descending indexing and loose index scan
    降序索引和减轻索引扫描

    Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general - these are features MySQL should get for all storage engines at some point.

    在我以前文章的评论中,尤其是 Gokhan 在这个中提到的,激发了我想写点关于降序索引和减轻索引扫描的东西,或者是 Gokhan 所谓的“改善的范围”支持。通常这些特性跟 Innodb 表都没有内在联系,它们将来在某些时候都能支持各种存储引擎。

    Descending indexes - This is something MySQL does not have at this point, but it was not where for so long at large extent because it is less problem than many people think. First - if index is ascending it does not mean it can’t be scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan could be as fast as forward scan - this is however where storage engines and operating systems come in play. For example certain operation systems might not do backward read-ahead which may slow it down a bit. Or some storage engines, such as MyISAM (for packed indexes) may have reverse scan being much slower than forward scan.

    降序索引 -- 现在 MySQL 还不支持这个功能,不过这比很多人想的那样,问题少多了。首先,如果索引是顺序的并不意味着它不能倒序扫描,实际上它表现得挺好的。这就是为什么 MySQL 能优化例如 ORDER BY col DESC 查询的索引。倒序扫描能和正序扫描一样快,不过这些是由存储引擎和操作系统来处理的。例如有些操作系统无法倒序读,这对速度有所降低。或者某些存储引擎,如 MyISAM (它压缩了索引) 倒序扫描时比正序扫描来的慢。

    So when do you really need Descending indexes ? Most typical case is when you want to order by two colums in different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order you will not be able to optimize this query well - external sort (”filesort”) will be needed. If you would be able to build index on price ASC, date DESC the same query could retrive data in aready sorted order.

    那么什么时候才真的需要倒序索引呢?很多典型的情况是当你想要对两个字段作不同方向的排序时:… ORDER BY price ASC, date DESC LIMIT 10。如果已经有了对 (price,date) 的正序索引,则不能较好地优化这个查询 -- 需要用到外部排序(“filesort”)。如果能建立 price ASC, date DESC 的索引,那么这个查询就能按照已经排好的顺序取出数据了。

    This is however something you can workaround by having something like “reverse_date” column and using it for sort. With MySQL 5.0 you even can use triggers to update it as real date updates so it becomes less ugly. In fact this is for example why you would see “reverse_timestamp” field in Wikipedia table structure.

    然而,常见的变通办法是创建一个“倒序数据”字段,并且利用它来排序。在 MySQL 5.0 中你甚至可以使用触发器来更新真实的数据使得更合适。这就是为什在 Wikipedia 的表结构中有一个 “reverse_timestamp” 字段的缘故。

    Loose index scan - Number of years ago when I just started using MySQL I thought it would have any optimization which could come to my mind. For example if I would have (A>0 and B>6) clause and index (A,B) I expected it would start looking at all values where A>0 instantly jumping to onces have B>6 by using index. It is possibe. So I was shocked and upset to find out it did not. And this optimization is still not implemented. This is very important item to remember when you designing your new applications or porting ones from other databases. Designing the indexes for MySQL you should only make sure queries use “=” for all keyparts in the last of index. Only last one is allowed to have “range” clauses, such as >, IN etc. All clauses which follow the range in the index will not use index for their operation.

    减少索引扫描 -- 多年前当我刚开始使用 MySQL 时,我想它也许有些优化方法能让我记住。例如如果有一个 (A>0 and B>6) 分句和索引 (A,B),我期望能使用索引来查找所有 A>0 的值,并且能立刻跳转到 B>6 的记录上,我想这是可行的。不过令我郁闷的是竟然不支持,并且这种优化方法还未实现。在设计新的应用程序或者移植数据库时,记住这个特点很重要。设计 MySQL 索引时只需设计保证能让索引最后的所有索引部分都使用 “=” 查询。只有最后一个索引部分才支持 “range” 分句、IN 等。所有在范围索引后面的分句都不会使用到索引。

    Let me give one more example KEY (A,B,C) A=5 and B>6 and C=7 In this case index will be used to check A=5 and B>6 cause. C=7 will not be using the index (rows with all C values will be retrieved from the index) and if this is not the index covered query you might rather shorten your key to KEY(A,B) to keep index smaller.

    举几个例子吧, 索引 (A,B,CP) 和 A=5 and B>6 and C=7 分句的情况下,索引会检索 A=5 和 B>6 的条件,C=7 则不会用到索引(所有包含 C 的记录都会从索引中检索得到)。这个时候如果任何查询都无需使用完整的索引的话,就可以缩短索引为 KEY(A,B),这样能让索引变小。

    The good news are Loose index scan implementation is finally on a way. In fact it was even implemented in MySQL 5.0, but only for very narrow case of aggregate queries.

    一个好消息是,减少索引扫描终究会以某种方式实现。MySQL 5.0 中其实已经实现了,不过只适用于少数情况的聚合查询。

    In general complete loose index scan implementation is one of my most wanted features for MySQL optimizer.
    P.S If you post queries in your comments please also explain which indexes do you have on the table. SHOW CREATE TABLE is the best. Otherwise I can get you wrong.

    常规意义上的完全减少索引扫描是我最想要实现的MySQL优化器特性。顺便提一下,如果你在我的帖子评论中贴上了查询语句,请顺便说明你的索引情况,最好是贴上 SHOW CREATE TABLE 的结果。

     
    展开全文
  • mysql 升序降序

    2019-10-03 07:01:33
    默认不指定,order by 按照升序排列。 asc:升序 desc:降序 转载于:https://www.cnblogs.com/sea-stream/p/11300321.html
  • mysql 8.0降序索引

    千次阅读 2019-01-08 16:18:52
    MySQL支持降序索引:DESC在索引定义中不再被忽略,但会导致按键降序存储键值。而且只有InnoDB存储引擎支持降序索引例如:创建表CREATE TABLE test.t (id INT NOT NULL auto_increment PRIMARY KEY,c1 INT, c2 INT);...
  • mysql 升序 降序

    千次阅读 2013-11-10 10:25:34
    desc 代表降序。 例:select * from test order by sort_order desc; asc 代表升序。 例:select * from test order by sort_order asc;
  • MySQL5.7 降序索引无效

    2020-09-12 22:01:44
    用alter table student add index IXSage(dept asc,age desc);...在已知表中添加索引,第二个降序索引还是显示升序 ![图片说明](https://img-ask.csdn.net/upload/202009/12/1599919283_386143.jpg)
  • mysql可以根据字段进行排序 其中,DESC表示降序,ASC表示升序 order by 字段名 DESC;按照字段名降序排序 order by 字段名 ASC;按照字段名升序排序
  • MYSQL降序排列

    千次阅读 2013-01-25 14:59:04
    今天查询时需要用到降序排列,老是出错,仔细检查SQL语法也没问题,后来才知道,原来order by要放在limit的前面。或者说limit要放在最后。 String sql = "select * from s_dayreport " + "where userId = "+...
  • mysql 升序和降序

    2020-04-26 14:35:49
    MySQL排序 有时候很容易记混,我这里把他记录了下: desc是降序 asc是升序(默认不写就是升序) MySql语句: 1.带条件的排序 select * from 表名 where Number=1 order by input_date desc -----------------...
  • MySQL中 升序为asc 降序为desc 例如:  升序:select * from 表名 order by 表中的字段 asc(MySQL中默认是升序排列,可不写) ;  降序:select * from 表名 order by 表中的字段 desc ; 若要进行同时一个升序...
  • MySQL8.0中的降序索引

    2021-01-19 21:41:00
    不过,在MySQL之前版本中,只支持升序索引,不支持降序索引,这会带来一些问题;在最新的MySQL 8.0版本中,终于引入了降序索引,接下来我们就来看一看。 降序索引 单列索引 (1)查看测试表结构 mysql> show ...
  • mysql 升序,降序

    2015-01-20 17:16:58
    一、 升序(从小到大)排序 SELECT id FROM table ORDER BY tid(需要排序的条件) ASC LIMIT 0,10;...二、 降序(从大到小)排序 SELECT id FROM table ORDER BY tid(需要排序的条件) DESC LIMIT 0,10;
  • 在使用数据库时,我们可能要将数据按照从小到大,或者从大到小的顺序排序。这样我们就用到了升降序排序。...降序:从大到小(desc) 也是上面表的数据。 例子:从大到小 语法:select * from stu or
  • mySql中,升序为asc,降序为desc。例如: 升序:select * from 表名 order by 表中的字段 asc(mysql中默认是升序排列,可不写) 降序:select * from 表名 order by 表中的字段 desc 若要进行同时一个升序,一个降序...
  • 什么是降序索引大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集。我们通常使用下面的语句来创建一个索引:create index idx_t1_bc...
  • Mysql中升序降序的用法 升序:select * from 表名 order by 表中的字段 asc(mysql中默认是升序排列,可以不写) 降序:select * from 表名 order by 表中的字段 desc 若要进行同时一个升序,一个降序,则:...
  • mysql的升序和降序

    千次阅读 2017-02-27 16:56:02
    mysql中升序和降序分别为desc和asc,一直记不清楚到底谁是升序,谁是降序,下面以sql语句和英文单词出发,更好的记清楚到底谁是升序,谁是降序。 1、select * from tableA order by number1 desc,number2 asc; ...
  • MySQL 8.0 新特性之降序索引

    千次阅读 2018-12-17 11:27:48
    MySQL 8.0 支持降序索引(Descending index):索引定义中的`DESC`不再被忽略,而是以降序方式存储索引键值。在之前的版本中,索引支持反向扫描,但是性能稍差一些。降序索引可以进行正向扫描,效率更高。当查询需要...
  • Mysql降序排序语法和HQL语句的语法

    千次阅读 2016-09-06 17:00:20
    一,hql语句的写法 数据库从大到小排序  String hql = "from Users u order by u.... sql语句按时间降序排序查询最新2条数据sql语句  select * from sendemailtable order by sendtime desc limit 2;
  • <p>I am trying to display MySQL database selected records in descending order based on the last id. but it's not working please see the SQL statement below <pre><code>SELECT * FROM product WHERE ...
  • MySQL问题一 升序和降序

    万次阅读 2016-04-12 11:33:02
    MySQL问题一 升序和降序 浅谈MySQL升序和降序,便于我们读取数据。MySQL 排序 我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。 如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的 ORDER BY 子句...
  • MySQL 8.0 之前的索引排序规则之前只允许 ASC 存储,创建时指定 DESC 也会被忽略。 8.0 版本为我们带来了 Descending Indexes 降序索引 ???????????? 只能使用 ASC 索引,带来的问题就是使用 DESC 会出现更多的 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,025
精华内容 810
关键字:

mysql降序

mysql 订阅