blocks|key|1235655|text|如果您只有100个数字，我建议您创建一个具有排名的视图，并将其用于后续处理。使用中间表似乎有点过头了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1235656|select+hn.*,
+++++++(select+count(*)+from+hundrednumbers+hn2+where+hn2.number+<=+hn.number
+++++++)+as+rank
from+HundredNumbers+hn|code-block|syntax|javascript|1235657|有了hundrednumbers(number)上的索引，这甚至会有不错的性能。|offset|length|style|CODE|1235658|您遇到的问题可能是原始数据中的重复。如果是这样的话，看看队伍可以帮助你弄清楚在这种情况下该怎么做。|1235659|entityMap^0|0|0|2|M|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If you have only 100 numbers, I would suggest that you create a view with a rank, and use that for subsequent processing. Using intermediate tables seems like overkill:

<pre><code>select hn.*,
 (select count(*) from hundrednumbers hn2 where hn2.number &lt;= hn.number
 ) as rank
from HundredNumbers hn
</code></pre>

With an index on <code>hundrednumbers(number)</code>, this will even have decent performance.

It is possible that the problem you are encountering is duplicates in the original data. If so, looking at the ranks can help you figure out what to do in this situation.

blocks|key|1279287|text|您正在进行字符串比较，并且这些比较遵循与数字数据类型不同的规则；我建议您更改排序表达式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1279288|ORDER+BY+CAST(HUNDREDNUMBERS.number+AS+UNSIGNED)+DESC%7CASC|code-block|syntax|javascript|1279289|如果需要分别支持负数或浮点数，也可以使用SIGNED或DECIMAL(M,+N)来代替UNSIGNED。|offset|length|style|CODE|1279290|或者(也是最好的)，您可以将number列更改为按其自身正确排序的类型；VARCHAR应该主要用于文本。|1279291|entityMap^0|0|0|K|6|R|D|17|8|0|E|6|10|7|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@$I|10|J|11|K|L]|$I|12|J|13|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$]]

You're doing string comparisons and those follow different rules than numeric data types; I would suggest to change your sort expressions:

<pre><code>ORDER BY CAST(HUNDREDNUMBERS.number AS UNSIGNED) DESC|ASC
</code></pre>

Instead of <code>UNSIGNED</code> you could also use <code>SIGNED</code> or <code>DECIMAL(M, N)</code> if you need to support negative numbers or floating points respectively.

Alternatively (and preferably), you could change the <code>number</code> column to a type that sorts properly by itself; <code>VARCHAR</code> should mostly be used for text.

blocks|key|5063808|text|您应该检查数据类型。确保这些数字至少存储为一个小数。其他数据类型可能导致排序关闭(这是一个非常常见的错误)。这看起来很简单，但从我对这个问题的理解来看，你的代码实际上看起来是正确的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5063809|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You should check the data types. Make sure the the numbers are stored as at least a decimal. Other data types can cause the sorting to be off (and is a quite common mistake). It seems simple, but your code actually looks to be correct from what my understanding is of the question.

blocks|key|1279338|text|经过长时间的思考和测试，我相信我终于破解了它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1279339|1)我把字段名"numbers“改成了DOUBLE+UNSIGNED。(最初我使用的是VARCHAR(50)+)|1279340|2)当您使用两个或更多具有相同字段名的表时，请在每个字段名前面加上其表名。我这样做了，它起作用了，正如你将在下面的完整查询中看到的那样。|1279341|3)原始数据多次出现相同的数字，即有几个值为100的行的实例。MySQL只将值为100的一行传输到表BESTQUARTER表中。(我不知道为什么)。|1279342|uniqueid++++%7C+id++++%7C+numbers+++
1+++200+100
2+++6+++100
3+++76++100
4+++64++99.009987655
5+++10++95.98765432
6+++11++11.98765432
7+++12++25.12
8+++13++53.173543
9+++153+72.87676
10++32++99|code-block|syntax|javascript|1279343|所以我添加了"GROUP+By“，并使用了ID字段。(注意:唯一id列是主键，id是唯一标识每个数字的唯一键)|1279344|这是新的代码|1279345|create+table+BESTQUATER++
select+
HUNDREDNUMBERS.uniqueid+,+
HUNDREDNUMBERS.id,+
HUNDREDNUMBERS.numbers
FROM+
HUNDREDNUMBERS
group+by+HUNDREDNUMBERS.id
ORDER+BY+HUNDREDNUMBERS.numbers++DESC++LIMIT+25|1279346|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|J|7|11|8|@]|9|@]|A|$K|L]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

After long hours of thinking and testing, i believe i finally cracked it.

1) I changed the fieldname "numbers" to DOUBLE UNSIGNED. 
(initially i was using VARCHAR(50) )

2) Whenever you are using two or more tables that have the same field names, prefix EVERY fieldname with its tablename.
I did that and it worked, as you shall see in the full query below.

3) the original data had multiple occurrences of the same numbers, 
ie there were several instances of rows with the value 100.
MySQL transferred only a single row with the value 100, into the table BESTQUARTER. (i don't know why).

<pre><code>uniqueid | id | numbers 
1 200 100
2 6 100
3 76 100
4 64 99.009987655
5 10 95.98765432
6 11 11.98765432
7 12 25.12
8 13 53.173543
9 153 72.87676
10 32 99
</code></pre>

So i added "GROUP By" and used the ID field.
(nb: "uniqueid" column is the primary key, "id" is a unique key that uniquely identifies each number)

Here's the new code

<pre><code>create table BESTQUATER 
select 
HUNDREDNUMBERS.uniqueid , 
HUNDREDNUMBERS.id, 
HUNDREDNUMBERS.numbers
FROM 
HUNDREDNUMBERS
group by HUNDREDNUMBERS.id
ORDER BY HUNDREDNUMBERS.numbers DESC LIMIT 25
</code></pre>

Im facing a unique challenge.

I got a table with 100 numbers called HUNDREDNUMBERS.
I want to select the best quarter (75 to 100 numbers), 
and place them into another table called BESTQUARTER.

I also want to select the worst quarter (1 to 25 numbers)
I want to place these into another table called WORSTQUARTER.

here's my Mysql code, so far, 

<pre><code>$Extract_Data = "
CREATE TABLE $BESTQUARTER 
SELECT 
HUNDREDNUMBERS.number
FROM 
HUNDREDNUMBERS order by 
HUNDREDNUMBERS.number desc LIMIT 25 ";
$QuerySuccess = mysql_query($Extract_Data, $connection);
</code></pre>

and for the other table....

<pre><code> $Extract_Data = "
 CREATE TABLE $WORSTQUARTER 
 SELECT 
 HUNDREDNUMBERS.number
 FROM 
 HUNDREDNUMBERS order by 
 HUNDREDNUMBERS.number asc LIMIT 25 ";
 $QuerySuccess = mysql_query($Extract_Data, $connection);
</code></pre>

The problem is that this script is not 100% correct every time. 
Notice the ASC and the DESC in the two queries. 
It's an ingenious way of trying to sort the numbers.

BTW, some of the numbers in the HUNDREDNUMBERS table have decimal points.
I need the data in the two new tables BESTQUARTER and WORSTQUARTER for further processing.

Any help is greatly appreciated

Advanced sorting and searching a mysql database using php and mysql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正面临着一个独特的挑战。我有一个有100个数字的表格，叫做HUNDREDNUMBERS。我想选择最好的季度(75到100个数字)，并将它们放入另一个名为BESTQUARTER的表中。我还想选择最差的季度(1到25个数字)，我想把它们放到另一个名为WORSTQUARTER的表中。这是我的Mysql代码，到目前为止，$E...

问使用php和mysql对mysql数据库进行高级排序和搜索
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用php和mysql对mysql数据库进行高级排序和搜索EN