我有一个mysql表,如下所示:
+----+------+
| id | rank |
+----+------+
| 1 | 2 |
+----+------+
| 2 | -1 |
+----+------+
| 3 | 5 |
+----+------+
| 4 | 1 |
+----+------+
| 5 | -1 |
+----+------+
| 6 | -1 |
+----+------+
| 7 | 8 |
+----+------+
| 8 | -1 |
+----+------+
现在我想按以下顺序获取ID:首先是
WHERE rank >= 1 ORDER BY rank ASC
之后:
WHERE rank = -1
如何仅在一个mysql_query()
中获取此信息?
尝试以下操作:
SELECT *
FROM mytable
WHERE rank >= 1
ORDER BY rank
UNION
SELECT *
FROM mytable
WHERE rank = -1
或者类似的东西:
SELECT *
FROM mytable
WHERE rank >= 1
ORDER BY CASE WHEN rank>=1
THEN 0
ELSE 1,rank
建议答案:
SELECT id FROM myTable WHERE rank >= 1 ORDER BY rank ASC
UNION
SELECT id FROM myTable WHERE rank = -1
根据我的理解,您想要一列id,从id WHERE rank >= 1 ORDER BY rank ASC
开始,到id WHERE rank = -1
结束。
上一个sql查询使用UNION
,它连接来自单独SELECT
查询的两个结果表。只有当您从每个SELECT
查询中生成了相同数量的列时,才能应用UNION
,所以如果以后您想增加获得的列数,请记住这一点。
您还可以使用ELT映射秩。
样本
SELECT *
FROM mytable
ORDER BY ELT(rank+2,99,0,1,2,3,4,5,6,7,8) ASC;