mysql - 我们如何使用 MySQL 合并具有精确计数的同一行？

Question

我有这样的输出:

number      pd_team  rating         att_attorney      law_firm                  patent_developer
496486      Networks  Auto              Desai           Guntin                      Wade Walker     
520859      Networks  Auto              Snyder          Hanley                      Wade Walker 
449269      Networks  Auto              Silverman       Akerman                     Wade Walker 
526112      Networks  Auto              Chang           Amin; Turocy                Wade Walker 
558843      Networks  Auto              Silverman       Baker Botts;                Wade Walker 
403805      Networks  Auto              Chang           Baker Hostetler             Wade Walker     
264896      Networks  Auto              Desai           Fay Kaplun & Marcin;        Wade Walker 
228780      Networks  Auto              Desai           Jackson Walker;             Wade Walker     
534337      Networks  Auto              Chang           Myers Bigel                 Wade Walker 
174237      Networks  Auto              Desai           Scott P. Zimmerman;         Dylan Reid  
441658      Networks  Auto - CON        Desai           Toler Law Group PC          Wade Walker 
498561      Networks  Auto - CON        Chang           Tong; Rea; Bentley          Wade Walker
452380      Networks  Auto              Chang           Workman Nydegger            Wade Walker

上表是 MySQL 查询的输出，我想要的实际输出是:

每个字段的计数，即

Network(13)
Auto(11)
Auto-CON(2)
Desai(5)
Snyder(1)
Silverman(2)
Chang(5)
same with other like wade walker(12)

这是我的 MySQL 查询，其中可以有多个过滤器条目，并且全部使用 AND。

SELECT number,salesforce_data.pd_team,salesforce_data.law_firm, salesforce_data.pd_team, salesforce_data.att_attorney,salesforce_data.patent_developer,salesforce_data.rating,salesforce_data.law_firm,salesforce_data.level1_taxonomy FROM application_data INNER JOIN salesforce_data on application_data.application_number= salesforce_data.sf_application_number WHERE salesforce_data.pd_team In ("Networks") AND salesforce_data.ip_manager_rating IN ("Auto","Auto - CON") AND salesforce_data.status="Filed"

快照:

Answer 1

你可以从这个开始...

SELECT number entity
     , 'pd_team'  attribute 
     , pd_team value
  FROM my_table
 UNION
SELECT number
     , 'rating'  attribute 
     , rating value
  FROM my_table
 UNION ...

从中得出结果是微不足道的