MySQL sort on a calculation
Is it possible to sort on a calculation of 2 rows in mySQL? For example, I have 2 rows, lp
and ap
I'm trying to do something like this:
SELECT * from myT开发者_运维问答bl WHERE 1 ORDER BY (lp/ap)
Which isn't throwing an error, but its also not sorting by the results of that calculation. Is there a way to do this, or do I have to store lp/ap in the database?
Yes, it is possible, and it does work. Check out the following test:
CREATE TABLE a(a INT, b INT);
INSERT INTO a VALUES (1, 1);
INSERT INTO a VALUES (1, 2);
INSERT INTO a VALUES (1, 3);
INSERT INTO a VALUES (1, 4);
INSERT INTO a VALUES (1, 5);
INSERT INTO a VALUES (1, 6);
INSERT INTO a VALUES (2, 1);
INSERT INTO a VALUES (2, 2);
INSERT INTO a VALUES (2, 3);
INSERT INTO a VALUES (2, 4);
INSERT INTO a VALUES (2, 5);
INSERT INTO a VALUES (2, 6);
SELECT a.a, a.b, (a/b) FROM a ORDER BY (a/b);
+------+------+--------+
| a | b | (a/b) |
+------+------+--------+
| 1 | 6 | 0.1667 |
| 1 | 5 | 0.2000 |
| 1 | 4 | 0.2500 |
| 2 | 6 | 0.3333 |
| 1 | 3 | 0.3333 |
| 2 | 5 | 0.4000 |
| 1 | 2 | 0.5000 |
| 2 | 4 | 0.5000 |
| 2 | 3 | 0.6667 |
| 2 | 2 | 1.0000 |
| 1 | 1 | 1.0000 |
| 2 | 1 | 2.0000 |
+------+------+--------+
SELECT a.a, a.b FROM a ORDER BY (a/b);
would return the same results.
SELECT *, (lp/ap) AS calculation from myTbl ORDER BY calculation
This should do the trick, provided the (lp/ap) is valid.
you would probably have more chance doing
SELECT *, (lp/ap) as n from myTbl ORDER BY n
精彩评论