mysql - 如何在 MySQL 查询中连接行？

Question

我正在下面运行此查询:

SELECT a.id as id_order, b.id as id_service,
   d.nome as service_name, 
   c.dente as tooth,
   (SELECT count(c.dente) 
    FROM labpedidoservicodente c 
    WHERE b.id = c.idLabPedidoServico) AS total,
    e.valorServico as cost
FROM   labpedido a 
   INNER JOIN labpedidoservico b 
           ON a.id = b.idLabPedido 
   INNER JOIN labpedidoservicodente c 
           ON a.id = c.idLabPedido 
   INNER JOIN labservico d 
           ON b.idLabServico = d.id 
   INNER JOIN labservicovalor e
           ON b.idLabServico = e.idLabServico
WHERE  a.id = '914'

我的结果是这样的:

order_id  service_id  service_name     tooth   total   cost
914          640      SERVICE NAME 1     11      3    80.00
914          640      SERVICE NAME 1     21      3    80.00
914          640      SERVICE NAME 1     38      3    80.00
914          641      SERVICE NAME 2     11      3    84.80
914          641      SERVICE NAME 2     21      3    84.80
914          641      SERVICE NAME 2     38      3    84.80

我想要的输出应该是这样的:

order_id  service_id  service_name     tooth   total   cost
914          640      SERVICE NAME 1    11-21    2    80.00
914          641      SERVICE NAME 2     38      1    84.60

问题是我需要将这些行连接到各自的“service_id”内的“tooth”列中，已经尝试了所有方法但没有成功，总数也是如此

Answer 1

将 c.dente 替换为 GROUP_CONCAT(c.dente SEPARATOR ' - ') 并在下面添加 GROUP BY service_id。