logo标签列表

按数字排序并保持分组的SQL排序

mysqlsqlsorting
8

感谢您抽出时间阅读并可能回答我的问题。

我有一个小的排序问题，如下所示：

我的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|
+-------+---+------+

按照同一“顾问”的最大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
1个回答
2
您可以在 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;

这里假设您想按“no”值进行排序。如果您想按总数排序，只需从子查询中删除该条件即可。
另请注意，我简化了日期比较。没有必要精确到秒 - 实际上，不建议使用“between”进行日期/时间比较，因为会导致时间组件的混淆。
谢谢您的回答。这正是我想要的。 - David Allios
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接