创建自定义 SQL 排序依据规则
我正在执行一个基本的选择操作,检索 MySQL 数据库中的 varchar 列。该列中存储的值是“作业编号”,并且全部采用以下形式:
J##-####
其中前两个 # 符号表示作业发生年份的最后两位数字,最后四位表示作业发生年份的最后两位数字。创造就业机会的顺序。
以下是职位编号的示例:
J11-0152(2011 年创建的第 152 个职位)。下一个是 J11-0153...
假设记录以随机顺序存储,是否可以检索记录并排序以便首先显示最新的作业?
例如 表中的记录按以下顺序存储:
1. J09-1893
2. J11-1323
3. J08-0011
4. J09-0234
5. J10-1232
6. J11-1111
将检索为:
1. J11-1323
2. J11-1111
3. J10-1232
4. J09-1893
5. J09-0234
6. J08-0011
我愿意接受任何可以在 MySQL 中完成的解决方案。我使用 CakePHP 作为应用程序框架,所以如果有人知道一个聪明的蛋糕解决方案也可以解决这个问题。
I'm doing a basic select operation retrieving a varchar column in a MySQL database. The values stored in the column are "job numbers", and all have the form:
J##-####
Where the first two # signs indicate the last two digits of the year the job took place, and the last four represent the order in which the jobs were created.
Here's an example of what a job number might look like:
J11-0152 (The 152nd job created in 2011). The next would be J11-0153...
Assuming the records are stored in random order, is there away to retrieve records, sorted so that the most recent jobs show first?
e.g.
The records in the table are stored in this order:
1. J09-1893
2. J11-1323
3. J08-0011
4. J09-0234
5. J10-1232
6. J11-1111
Would be retrieved as:
1. J11-1323
2. J11-1111
3. J10-1232
4. J09-1893
5. J09-0234
6. J08-0011
I'm open to any solution that can be accomplished in MySQL. I'm using CakePHP as the app framework, so if anyone knows of a clever cake solution to the problem that could work too.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
首先:
然后:
“失败得可怕”对我来说意味着“很慢”。
First:
Then:
"Fails horribly" means "is slow" to me.
使用 SUBSTRING() 获取您感兴趣的字符串部分:
所以在您的情况下它应该是:
Use SUBSTRING() to get the part of the string you are interested in:
So in your case it should be:
再次尝试您的
jobnumber DESC
查询,或者说明失败的原因。也许提供的示例输出不准确。Try your
jobnumber DESC
query again, or say why it fails. Maybe provide sample output that is inaccurate.两位数的日期和工作编号仍应正确排序,即使按字典顺序也是如此。但是,如果您涉及 2000 年之前的数据,那么您需要这个:(
包括示例表 - 只需忽略)
至于
索引
(因为您标记了它),有两个选项:The 2-digit dates and job numbers should still sort correctly, even lexicographically. But, if you had pre-2000 data involved, then you need this:
(sample table included - just ignore)
As for
indexing
(since you tagged it), two options: