mysql - SQL 按数字排序并保持分组

Question

感谢您花时间阅读并回答我的问题。

我给你展示了一个排序的小问题:

我的 SQL 查询是:

SELECT p.advisor_create, COUNT(p.id) AS Nb, IF(p.cancel_advisor IS 
NULL, "no", "yes") AS refund
FROM payment p
WHERE p.type = 'CESSATION'
AND p.date BETWEEN '2017-01-01 00:00:00' AND '2017-11-31 23:59:59'
GROUP BY p.advisor_create, refund
ORDER BY p.advisor_create, Nb DESC

我从中得到的数据是这样的:

+-------+---+------+
|advisor| NB|refund|
+-------+---+------+
| 170432| 50|    no|
| 170432|  4|   yes|
| 175222| 30|    no|
| 175222|  3|   yes|
| 182985|304|    no|
| 182985| 19|   yes|
| 362912|360|    no|
| 362912| 13|   yes|
+-------+---+------+

我会这样排序:

+-------+---+------+
|advisor| NB|refund|
+-------+---+------+
| 362912|360|    no|
| 362912| 13|   yes|
| 182985|304|    no|
| 182985| 19|   yes|
| 170432| 50|    no|
| 170432|  4|   yes|
| 175222| 30|    no|
| 175222|  3|   yes|
+-------+---+------+

按相同“顾问”的 MAX(NB) 排序并将顾问的两行分组在一起。

再次感谢您的帮助。

解决方案:为此感谢@gordon-linoff

SELECT p.advisor_create, COUNT(p.id) AS Nb,
   IF(p.cancel_advisor IS NULL, 'no', 'yes') AS refund
FROM payment p
WHERE p.type = 'CESSATION' AND
  p.date >= '2017-01-01' AND 
  p.date < '2017-12-01'
GROUP BY p.advisor_create, refund
ORDER BY (SELECT COUNT(*)
      FROM payment p2
      WHERE p2.advisor_create = p.advisor_create AND
            p2.type = 'CESSATION' AND
            p2.date >= '2017-01-01' AND 
            p2.date < '2017-12-01' AND
            p2.cancel_advisor IS NULL
     ) DESC,
     p.advisor_create, Nb DESC

Answer 1

您可以在 order by 中使用子查询。这使您可以:

SELECT p.advisor_create, COUNT(p.id) AS Nb,
       IF(p.cancel_advisor IS NULL, 'no', 'yes') AS refund
FROM payment p
WHERE p.type = 'CESSATION' AND
      p.date >= '2017-01-01' AND 
      p.date < '2017-12-01'
GROUP BY p.advisor_create, refund
ORDER BY (SELECT COUNT(*)
          FROM payment p2
          WHERE p2.advisor_create = p.advisor_create AND
                p2.type = 'CESSATION' AND
                p2.date >= '2017-01-01' AND 
                p2.date < '2017-12-01' AND
                p2.cancel_advisor IS NULL
         ),
         p.advisor_create, Nb DESC;

假设您要按“否”值排序。如果您想按总数排序，只需从子查询中删除该条件即可。

另请注意，我简化了日期比较。没有理由去秒 -- 事实上，不建议将 between 用于日期/时间比较，因为时间组件会引起混淆。