创建自定义 SQL 排序依据规则

发布于 2024-10-17 11:40:22 字数 607 浏览 4 评论 0原文

我正在执行一个基本的选择操作,检索 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

九八野马 2024-10-24 11:40:23

首先:

CREATE INDEX idx_table_jobnumber ON table (jobnumber);

然后:

select jobnumber FROM table ORDER BY jobnumber DESC

“失败得可怕”对我来说意味着“很慢”。

First:

CREATE INDEX idx_table_jobnumber ON table (jobnumber);

Then:

select jobnumber FROM table ORDER BY jobnumber DESC

"Fails horribly" means "is slow" to me.

长发绾君心 2024-10-24 11:40:23

使用 SUBSTRING() 获取您感兴趣的字符串部分:

SELECT SUBSTRING(myFieldName, from, to) AS myFieldAlias ORDER BY myFieldAlias DESC

所以在您的情况下它应该是:

SELECT SUBSTRING(job_number, 1, 3) as location, SUBSTRING(job_number, 5, 4) AS jobno ORDER BY location DESC, jobno DESC

Use SUBSTRING() to get the part of the string you are interested in:

SELECT SUBSTRING(myFieldName, from, to) AS myFieldAlias ORDER BY myFieldAlias DESC

So in your case it should be:

SELECT SUBSTRING(job_number, 1, 3) as location, SUBSTRING(job_number, 5, 4) AS jobno ORDER BY location DESC, jobno DESC
不语却知心 2024-10-24 11:40:23

再次尝试您的 jobnumber DESC 查询,或者说明失败的原因。也许提供的示例输出不准确。

Try your jobnumber DESC query again, or say why it fails. Maybe provide sample output that is inaccurate.

坐在坟头思考人生 2024-10-24 11:40:22

两位数的日期和工作编号仍应正确排序,即使按字典顺序也是如此。但是,如果您涉及 2000 年之前的数据,那么您需要这个:(

包括示例表 - 只需忽略)

drop table if exists jobnumbers ;
create table jobnumbers (id int primary key, number varchar(10)); 
insert jobnumbers values
(  1, 'J09-1893'),
(  2, 'J11-1323'),
(  3, 'J08-0011'),
(  4, 'J09-0234'),
(  5, 'J10-1232'),
(  6, 'J11-1111'),
(  9, 'J99-1111');

select *
from jobnumbers
order by 
  case when number>'J5' then 1900 else 2000 end desc, number desc;

至于索引(因为您标记了它),有两个选项:

  1. 您必须将其拆分分成多个列以便能够按年份建立索引,然后工作编号
  2. 使用 4 位数年份。

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)

drop table if exists jobnumbers ;
create table jobnumbers (id int primary key, number varchar(10)); 
insert jobnumbers values
(  1, 'J09-1893'),
(  2, 'J11-1323'),
(  3, 'J08-0011'),
(  4, 'J09-0234'),
(  5, 'J10-1232'),
(  6, 'J11-1111'),
(  9, 'J99-1111');

select *
from jobnumbers
order by 
  case when number>'J5' then 1900 else 2000 end desc, number desc;

As for indexing (since you tagged it), two options:

  1. you would have to split that into multiple columns to be able to index on year, then job number
  2. Use 4-digit years.
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文