mysql按照数字分组_mysql – 按数字排序SQL并保持分组

感谢您花时间阅读并可能回答我的问题.

我有一个排序问题,我告诉你：

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