从MySQL返回结果时的数字顺序 [英] Numeric order when returning results from MySQL
本文介绍了从MySQL返回结果时的数字顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在数据库中的表格中有以下类型的标题:
主题1 blah blah
2 blah blah
主题3 blah blah
...
主题10 blah blah
主题11 blah blah
等...
select查询将总是返回如下结果:
主题1 blah
主题10 blah blah
主题11 blah
...省略主题2,主题3等...直到所有的青少年都会得到主题2等...
如何获得我的结果为:
主题1
主题2
主题3
....一直到主题9,然后只有主题10?
解决方案
这是您的数据库设计的问题。主题编号应存储为整数。如果您无法更改设计,请改用此查询:
SELECT title
FROM table1
ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title,'',2),'',-1)
AS UNSIGNED);
结果:
'主题1 foo'
'主题2 bar'
'主题10 baz'
测试数据:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1(title VARCHAR(100)NOT NULL);
INSERT INTO table1(title)VALUES
('topic 1 foo'),
('topic 2 bar'),
('topic 10 baz');
I have the following types of titles in a table in my db:
Topic 1 blah blah
Topic 2 blah blah
Topic 3 blah blah
...
Topic 10 blah blah
Topic 11 blah blah
etc...
The select query will always return the results like this:
Topic 1 blah
Topic 10 blah blah
Topic 11 blah
...leaving out Topic 2, Topic 3 etc... until after all the teens will I get Topic 2 etc...
How can I get my results as:
Topic 1
Topic 2
Topic 3
.... all the way to Topic 9 and only then have Topic 10?
解决方案
This is a problem with your database design. The topic number should be stored as an integer. If you can't change the design, try this query instead:
SELECT title
FROM table1
ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', 2), ' ', -1)
AS UNSIGNED);
Result:
'topic 1 foo' 'topic 2 bar' 'topic 10 baz'
Test data:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (title VARCHAR(100) NOT NULL);
INSERT INTO table1 (title) VALUES
('topic 1 foo'),
('topic 2 bar'),
('topic 10 baz');
这篇关于从MySQL返回结果时的数字顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文