gpt4 book ai didi

sql - MySQL 查询变得疯狂?

转载 作者:可可西里 更新时间:2023-11-01 07:49:23 26 4
gpt4 key购买 nike

谁能告诉我为什么会发生以下情况,以及如何解决?

我有一个带有 ORDER BY 子句的 MySQL 查询,看起来像这样......

ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC

所以它应该按照“有效”评级对结果进行排序,如..

1st. 10 up - 1 down = 9 effective
2nd. 10 up - 5 down = 5 effective
3rd. 10 up - 7 down = 3 effective

但是,正如您在 my page here 上看到的那样, 它把它们排序错了,然后给我这个..

1st. 1 up - 3 down = -2 effective
2nd. 16 up - 6 down = 10 effective
3rd. 15 up - 5 down = 10 effective

显然,第一名的行不应该在那里。


更多信息..

CREATE TABLE dictionary_definitions (
did_id int(11) unsigned NOT NULL auto_increment,
did_wordid int(11) unsigned NOT NULL default '0',
did_userid int(11) unsigned NOT NULL default '0',
did_status tinyint(1) unsigned NOT NULL default '0',
did_date int(11) NOT NULL default '0',
did_definition text,
did_example text,
did_votecheck int(11) NOT NULL default '0',
did_voteup smallint(5) unsigned NOT NULL default '0',
did_votedown smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (did_id),
KEY (did_wordid),
KEY (did_userid)
) ENGINE=MyISAM;

SELECT a.did_id, a.did_userid, a.did_definition, a.did_example,
a.did_votecheck, a.did_voteup, a.did_votedown, b.user_name, b.user_extra8
FROM dictionary_definitions AS a LEFT JOIN users AS b ON a.did_userid=b.user_id
WHERE did_wordid=4
ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC LIMIT 0, 5

最佳答案

这是一个已知的 issue regarding subtraction from unsigned integers .

整数值之间的减法,其中一个是 UNSIGNED 类型,产生一个无符号的默认结果。如果结果本来是负面的,它就变成了最大整数值。如果启用了 NO_UNSIGNED_SUBTRACTION SQL 模式,则结果为消极的。

引用:Numeric Types

关于sql - MySQL 查询变得疯狂?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1517556/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com