GROUP_CONCAT不计

Question

工作，我用

SELECT 
GROUP_CONCAT(DISTINCT `a`.`IDperson` SEPARATOR ', ') AS `person`, 
COUNT(`a`.`IDjobs`) AS `total` 
FROM 
`a` 
GROUP BY `a`.`ID_person` 
ORDER BY `total` DESC 

和我需要的是找回像

person  total 
2342   98 
1342   75 
3844   70 
1705   62 
3309   53 
5918, 1328  52 
1503, 1890  46 
21004, 6536  45 

的结果，但它回来就像它不工作 GROUP_CONCT不能正常工作

person  total 
    2342   98 
    1342   75 
    3844   70 
    1705   62 
    3309   53 
    5918   52 
    1328   52 
    1503   46 
    1890   46 
    21004   45 
    6536   45 

Answer 1

看起来你需要按照计数的数量来分组，所以你应该使用

select GROUP_CONCAT(DISTINCT t.IDperson SEPARATOR ', ') AS person, t.total 
from (
     select DISTINCT a.IDperson as IDPerson, COUNT(a.IDjobs) AS `total` 
     FROM a 
     GROUP BY a.ID_person) t 
group by t.total 
ORDER BY t.total DESC 

Answer 2

我猜测，你想：

SELECT numjobs, GROUP_CONAT(idperson SEPARATOR ', ' ORDER BY idperson) as persons 
FROM (SELECT idperson, COUNT(*) as numjobs 
     FROM a 
     GROUP BY idperson 
    ) ap 
GROUP BY numjobs 
ORDER BY numjobs DESC; 

Answer 3

GROUP_CONCAT()完美。既然你GROUP BY `a`.`ID_person`，每组包含`a`.`ID_person`只有一个值，所以你得到的结果。您可能想要`GROUP BY `a`.`IDjobs`：

SELECT 
    GROUP_CONCAT(DISTINCT `a`.`IDperson` SEPARATOR ', ') AS `person`, 
    COUNT(`a`.`IDjobs`) AS `total` 
FROM 
    `a` 
GROUP BY `a`.`IDjobs` 
ORDER BY `total` DESC