mysql - mysql 中字符串列的 DISTINCT 子句

Question

我在MySQL数据库上使用了sql查询:

SELECT 
  DISTINCT Sp_Name,
  CONCAT('SP_',Sp_Id) AS searchPayerId
FROM  
  ServiceProviders 
WHERE 
  Comp_Id= 3 
  AND Sp_Id NOT IN (1,2)
  AND Sp_Name != ''
GROUP BY 
  Sp_Id

我仍然得到以下输出。为什么这个重复的“001 拖车”？

Sp_Name                                 searchPayerId
-----------------------------------------------------
#1 24 Hour Emergency Towing                SP_50094
'Triple T' Pts&Equip/Warsaw                SP_65699
0 00 Anytime Towing                        SP_50095
0 00 Anywhere Towing                   SP_50096
001 Towing                                 SP_50097
001 Towing                                 SP_50098
001 Towing                                 SP_50099
001 Towing                                 SP_50100
1 - 800 - Tow - Help Inc.                  SP_55040
1 2 3 Towing                           SP_27761
1 24 Hour Affordable Towing                SP_50101

我想得到以下输出:

   Sp_Name                              searchPayerId

-----------------------------------------------------
#1 24 Hour Emergency Towing                SP_50094
'Triple T' Pts&Equip/Warsaw                SP_65699
0 00 Anytime Towing                        SP_50095
0 00 Anywhere Towing                   SP_50096
001 Towing                                 SP_50097_50098_50099_50100
1 - 800 - Tow - Help Inc.                  SP_55040
1 2 3 Towing                           SP_27761
1 24 Hour Affordable Towing                SP_50101

我怎样才能得到它？

Answer 1

您在 Sp_Name 上使用 GROUP BY 和 Sp_Id 和 DISTINCT，您必须使用 GROUP BY 结果Sp_Name 就像

SELECT 
DISTINCT Sp_Id,
Sp_Name,
CONCAT('SP_',Sp_Id) AS searchPayerId
FROM  
ServiceProviders 
WHERE 
Comp_Id= 3 
AND Sp_Id NOT IN (1,2)
AND Sp_Name != ''
GROUP BY 
Sp_Name

Sql Fiddle希望有道理