我有两张桌子:
1) profiles
+----+---------+
| id | name |
+----+---------+
| 1 | WILLIAM |
| 2 | JOHN |
| 3 | ROBERT |
| 4 | MICHAEL |
| 5 | JAMES |
| 6 | DAVID |
| 7 | RICHARD |
| 8 | CHARLES |
| 9 | JOSEPH |
| 10 | THOMAS |
+----+---------+
2) request_for_friendship
+----+---------+-------+
| id | from_id | to_id |
+----+---------+-------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 8 |
| 5 | 4 | 1 |
| 6 | 9 | 1 |
+----+---------+-------+
我需要得到所有profiles
进行一些排序并将其加入request_for_friendship
例如,对所有用户进行某种排序:
mysql> SELECT *
-> FROM profiles
-> ORDER BY name ASC;
+----+---------+
| id | name |
+----+---------+
| 8 | CHARLES |
| 6 | DAVID |
| 5 | JAMES |
| 2 | JOHN |
| 9 | JOSEPH |
| 4 | MICHAEL |
| 7 | RICHARD |
| 3 | ROBERT |
| 10 | THOMAS |
| 1 | WILLIAM | <-- WILLIAM IS LAST!
+----+---------+
一切看起来都很好,排序已经存在。之后我加入request_for_friendship
我的 sotring 会崩溃:
mysql> SELECT * FROM
-> (
-> SELECT *
-> FROM profiles
-> ORDER BY name ASC
-> ) as users
-> LEFT JOIN request_for_friendship
-> AS request_for_friendship_copy
-> ON
-> (
-> request_for_friendship_copy.from_id = 1
-> AND
-> request_for_friendship_copy.to_id = users.id
-> )
-> OR
-> (
-> request_for_friendship_copy.from_id = users.id
-> AND
-> request_for_friendship_copy.to_id = 1
-> );
+----+---------+------+---------+-------+
| id | name | id | from_id | to_id |
+----+---------+------+---------+-------+
| 2 | JOHN | 1 | 1 | 2 |
| 3 | ROBERT | 2 | 1 | 3 |
| 8 | CHARLES | 3 | 1 | 8 |
| 4 | MICHAEL | 5 | 4 | 1 |
| 9 | JOSEPH | 6 | 9 | 1 |
| 1 | WILLIAM | NULL | NULL | NULL | <-- WILLIAM IN THE MIDDLE!
| 5 | JAMES | NULL | NULL | NULL |
| 6 | DAVID | NULL | NULL | NULL |
| 7 | RICHARD | NULL | NULL | NULL |
| 10 | THOMAS | NULL | NULL | NULL |
+----+---------+------+---------+-------+
How to JOIN LEFT
with 原始排序 saving?
我无法对结果进行排序 JOIN LEFT
因为当我这样做时ORDER BY
before JOIN
需要〜0.02秒in my db(~1 000 000 个用户)但是当我这样做时ORDER BY
after JOIN
大约需要 3.2 秒,时间非常长:(
演示:rextester.com/DLLM29415
演示:http://sqlfiddle.com/#!9/167792/1
在sqlfiddle中命令被保存!但如何呢? MySQL 5.6 保存命令吗?