SQLite 最小/最大奇怪行为
我有一个排名表，其架构定义为排名（id，键，值），其中键是主键
sqlite> .schema rank
CREATE TABLE 'rank' ( ID VARCHAR, KEY VARCHAR, VALUE VARCHAR NOT NULL );
CREATE INDEX 'rank_id___djklaf3451jlZZZRFfa___' ON 'rank' ( ID );
sqlite> select * from rank;
-----------------------------------------------------
Tymb-W64uwvM|Tymc8LPQnxBg|5
TymdPRdFpBcE|TymdSsaIFhuI|2
TymdPRdFpBcE|TymdjkGgExcE|3
TymeVf6N1RH4|TymeZGxydCJ8|3
TymeVf6N1RH4|Tymeecz1ORW8|20
sqlite> select id,min(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|2
TymeVf6N1RH4|20
sqlite> select id,max(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|3
TymeVf6N1RH4|3
正如您所看到的，最小和最大函数的第三个结果都是不正确的。 sqlite版本是3.6.23。
有什么建议吗？
I have a rank table and its schema is defined as rank(id, key,value) where key is the primary key
sqlite> .schema rank
CREATE TABLE 'rank' ( ID VARCHAR, KEY VARCHAR, VALUE VARCHAR NOT NULL );
CREATE INDEX 'rank_id___djklaf3451jlZZZRFfa___' ON 'rank' ( ID );
sqlite> select * from rank;
-----------------------------------------------------
Tymb-W64uwvM|Tymc8LPQnxBg|5
TymdPRdFpBcE|TymdSsaIFhuI|2
TymdPRdFpBcE|TymdjkGgExcE|3
TymeVf6N1RH4|TymeZGxydCJ8|3
TymeVf6N1RH4|Tymeecz1ORW8|20
sqlite> select id,min(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|2
TymeVf6N1RH4|20
sqlite> select id,max(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|3
TymeVf6N1RH4|3
As you can see the 3rd result for both the min and max functions are incorrect. The sqlite version is 3.6.23.
Any advice?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是因为
value是字符类型（特别是varchar）而不是数字类型。如果您要对字符数据进行排序，结果如下：
如果您执行以下命令，您会看到以下结果：
如果您希望按数字排序，则应将列定义为数字类型，例如
integer.It's because
valueis a character type (specificallyvarchar) rather than a numeric type.If you're sorting character data, it comes out as:
which is what you'd see if you executed:
If you want it to sort numerically, the column should be defined as a numeric type, such as
integer.这是因为您输入了第三个参数 VARCHAR。答案是“正确”，因为它是 VARCHAR 排序。如果您输入了第三个参数 INTEGER，您可能会更喜欢结果。即您的 CREATE TABLE 语句应该是：
否则，如果 VALUE 必须保持为 VARCHAR，您应该尝试使用以下 select 语句进行动态转换，但是，正如其他人所说，这对性能来说并不是很好：
It's because you've typed your 3rd parameter VARCHAR. The answer is 'correct' because it's VARCHAR sorting. If you had typed your 3rd parameter INTEGER, you would probably like the result much better. i.e. your CREATE TABLE statement should have been:
Otherwise, if the VALUE must stay as VARCHAR you should try the following select statement to cast on the fly, but, as others have said, this is not great for performance: