MySQL - 带 GROUP_CONCAT 的复杂查询...单表

Question

我有下表:

id | billingno | location
-------------------------
1  | 9999999   | Toronto
2  | 9999999   | Toronto
3  | 7777777   | Toronto
4  | 7777777   | Quebec

我需要一个查询来生成如下所示的内容:

location | total | display
--------------------------
Toronto  | 3     | 9999999 - 2, 7777777 - 1
Quebec   | 1     | 7777777 - 1

因此，它按位置分组，显示该位置的 billingno 总数，然后显示列列出每个 billingno 以及它们出现在该位置的次数。我已经尝试写这个有一段时间了，我最接近的尝试是:

SELECT location, COUNT(*) AS total, GROUP_CONCAT(DISTINCT CAST(CONCAT(CONVERT(billingno,CHAR(16)), ' - ', THIS_COUNT_PART_FOR_EACH_LOCATION_IN_DISPLAY_DOESNT_WORK)AS CHAR)
SEPARATOR ' - ') AS display
FROM table GROUP BY location
ORDER BY COUNT(*) DESC

它为我提供了我需要的一切，只是我一生都无法弄清楚如何计算所显示的每个帐单的数量。如果我使用 COUNT() 它会给我一个关于分组的错误。请帮忙!

哦，我还必须使用转换为字符，以便它在 phpMyAdmin 中显示为文本而不是 BLOB。再次感谢!

Answer 1

示例数据:

create table location (
    id int,
    billingno varchar(10),
    location varchar(10)
);

insert into location
select 1, '9999999', 'Toronto' union 
select 2, '9999999', 'Toronto' union 
select 3, '7777777', 'Toronto' union 
select 4, '7777777', 'Quebec' ;

查询:

select 
    location, 
    sum(qty) as total, 
    group_concat(concat(billingno, ' - ', cast(qty as char(7))) 
        order by qty desc separator ', '
    ) as display
from (
    select billingno, location, count(*) as qty
    from location
    group by  billingno, location
) t
group by location
order by total desc

结果:

location    total   display
Toronto     3       9999999 - 2, 7777777 - 1
Quebec      1       7777777 - 1