自定义MySQL排序

Question

我有一个需要自定义排序的查询，简化到最少应该像这样：

SELECT u.*, p.*, p.id as product_id
FROM users u, products p 
WHERE u.id = p.user_id
ORDER BY product_id DESC

我得到了一组类似以下的行：

UserID        ProductID
     2                5
     2                4
     3                3
     1                2
     1                1

但我希望它能够实际地像这样排序（这样就不会出现相邻的两个用户ID）：

 UserID        ProductID
     1                2
     2                4
     3                3
     2                5
     1                1

这是否可能使用MySQL实现，还是需要一些PHP技巧？

- Adam Skiba

---

我的意思是，[1,1,2,2,3] 对于用户ID来说是一个糟糕的排序方式，我需要相邻的UserID是不同的数字，如果是从2到1或者从3到2也可以，但不能是从1到1或者从2到2。 - Adam Skiba

只出于好奇，这背后的理由是什么？ - peterm

如果可能的话（考虑到您可能在某个时候有给定用户ID的权重，这可能不允许您这样做），为此制作SQL查询甚至是浪费时间和资源的，因此请检索数据并使用您的PHP魔术代码处理它。 - Prix

我必须同意Prix的观点，你可以用不太糟糕的SQL查询来_近似_它，但是要完全解决它，我认为PHP解决方案会_简单得多_。 - Joachim Isaksson

好的，伙计们，我也同意，PHP是解决方案。谢谢大家。 - Adam Skiba

Answer 1

解决这个问题的标准方法是枚举重复的行，然后按该值排序：

select t.*
from (SELECT u.*, p.*, p.id as product_id,
             row_number() over (partition by u.id order by (select NULL)) as seqnum
      FROM users u join
           products p 
           on u.id = p.user_id
     ) t
order by seqnum, id;

只要没有一个用户有一个非常长的序列（就像你的例子一样），这将起作用。

没有“永远有效”的解决方案，因为很容易想出一个目标不可能实现的情况。

- Gordon Linoff

Answer 2

在这里将您排序后的结果存入一个数组中。然后执行类似以下的操作。

 $records = $res->fetchAll();
 $count = count($records);
 $records = array_chunk($records, ceil(count($records)/2);
 $unsorted = array();
 for($x = 0; $x < $count; $x++){
      $unsorted[] = $records[$x%2][floor($x/2)];
 }

- Orangepill

Answer 3

请考虑以下内容...

CREATE TABLE sortable(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,player_id INT NOT NULL);

INSERT INTO sortable(player_id) VALUES (1),(1),(2),(3),(4),(3),(3),(2),(1),(2),(4),(4);

SELECT * FROM sortable;
+----+-----------+
| id | player_id |
+----+-----------+
|  1 |         1 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         4 |
|  6 |         3 |
|  7 |         3 |
|  8 |         2 |
|  9 |         1 |
| 10 |         2 |
| 11 |         4 |
| 12 |         4 |
+----+-----------+

SELECT x.*,COUNT(*) rank FROM sortable x JOIn sortable y ON y.player_id = x.player_id AND y.id <= x.id GROUP BY x.id ORDER BY player_id,rank;
+----+-----------+------+
| id | player_id | rank |
+----+-----------+------+
|  1 |         1 |    1 |
|  2 |         1 |    2 |
|  9 |         1 |    3 |
|  3 |         2 |    1 |
|  8 |         2 |    2 |
| 10 |         2 |    3 |
|  4 |         3 |    1 |
|  6 |         3 |    2 |
|  7 |         3 |    3 |
|  5 |         4 |    1 |
| 11 |         4 |    2 |
| 12 |         4 |    3 |
+----+-----------+------+

SELECT x.*,COUNT(*) rank FROM sortable x JOIn sortable y ON y.player_id = x.player_id AND y.id <= x.id GROUP BY x.id ORDER BY rank;
+----+-----------+------+
| id | player_id | rank |
+----+-----------+------+
|  1 |         1 |    1 |
|  3 |         2 |    1 |
|  4 |         3 |    1 |
|  5 |         4 |    1 |
|  2 |         1 |    2 |
|  8 |         2 |    2 |
|  6 |         3 |    2 |
| 11 |         4 |    2 |
|  9 |         1 |    3 |
| 10 |         2 |    3 |
|  7 |         3 |    3 |
| 12 |         4 |    3 |
+----+-----------+------+

- Strawberry

Answer 4

所以，如果你的问题只是不想让两个具有相同ID的记录相邻出现，我认为最简单的方法是使用

 SELECT u.*, p.*, p.id as product_id
FROM users u, products p 
WHERE u.id = p.user_id
ORDER BY user_id%2 DESC

或者你甚至可以使用除了2之外的其他数字来满足你想要的任何特定顺序...

- balaji

---

我可能漏掉了什么，但是根据产品ID排序对用户ID排序除了随机化效果外，还有什么作用呢？这样可以防止相邻的相等值吗？ - James Green