php - MySQL 在整数零问题上签署了 Order By

Question

我目前在 MyISAM 表中设置了一个有符号整数，

查询是这样的:

SELECT * from some_view ORDER BY value DESC, dateAdded DESC

我收到这个订单:

1
2
-1
-2
0
0

零值不被视为 +/- 但我需要它们大于负值!!!

这是我的实际查询:

SELECT * FROM vw_answer sa left join vw_answer_votes av on av.answerID = sa.id WHERE sa.id = 77 ORDER BY av.vote, dateAdded

我有以下内容:

tbl_users
tbl_solutions - Indexes tbl_users.id as authorID
tbl_solution_answers - Indexes tbl_solutions.id as solutionID
tbl_solution_answer_votes - Indexes tbl_users.id as voterID and tbl_solution_answers
as answerID

tbl_solution_answer_votes 只有一个无索引列，其中包含 -1 或 1，具体取决于用户的投票。这是一个带符号的整数。

当我选择答案时，我的 View 选择了 tbl_solution_answers 和总和(tbl_solution_answer_votes.vote)

一切正常，除了对有符号整数的排序。

编辑:投票表中的值仅在用户投票后才存在，否则根本不存在。我认为我需要这样的东西:

SELECT * FROM tbl_answers sa right join vw_answer_votes av on av.answerID = sa.id
ORDER BY av.vote > 0 desc, av.vote IS NULL, av.vote < 0 desc,  dateAdded DESC

Answer 1

您正在订购两个字段，value 和 dateadded，尝试取出您的添加日期。

create table order_by_neg_test
    (`id` int(11) unsigned not null auto_increment, 
    `value` int(11) signed not null, 
     primary key (`id`)) engine=myisam default charset=utf8;

我插入了一些随机值，结果如下:

# No order
mysql> select * from order_by_neg_test;
+----+-------+
| id | value |
+----+-------+
|  1 |    45 |
|  2 |     1 |
|  3 |     0 |
|  4 |    -1 |
|  5 |    17 |
|  6 |    27 |
|  7 |    -1 |
+----+-------+
7 rows in set (0.00 sec)

# With an order
mysql> select * from order_by_neg_test order by value desc;
+----+-------+
| id | value |
+----+-------+
|  1 |    45 |
|  6 |    27 |
|  5 |    17 |
|  2 |     1 |
|  3 |     0 |
|  4 |    -1 |
|  7 |    -1 |
+----+-------+
7 rows in set (0.00 sec)

注意顺序，0 > -1。