MySQL GROUP BY具有特定条件

Question

我使用的是MySQL 5.5。这是我的查询，第一部分显示有关该公司及其车辆的信息，第二部分仅显示公司信息。

我做了UNION部分，因为一些小型客户没有任何车辆，而且在第一部分，join有车辆，所以这些车辆没有出现。

问题是什么，第一部分和第二部分的一些寄存器有相同的smallClient，由于在车辆部分我已经显示了公司信息，我想按分组，前提是它们是相同的idClient和相同的部门，并且numberPlate不为空(如果为空，那没关系，我只想删除那些在车辆部件中已经显示的冗余原因信息的寄存器(

有些公司也可以有多个车牌，而且它们不能分组，因为每个车牌都有自己的文档(这个查询比实际的要小(。

现在的结果是什么：

idClient  company          department    numberplate
3345      TONY FERGUSON    Commercial    null
3345      TONY FERGUSON    Financial     null
3345      TONY FERGUSON    Commercial    8453JVD

在本例中，第一个寄存器应该消失，因为该客户端有一个numberPlate，而同一部门的寄存器(第一个(是多余的，因为第三个寄存器显示相同。

另一个例子：

idClient  company          department               numberplate
1267      TERRY SL         Distribution - France    null
1267      TERRY SL         Distribución - France    6381JHZ
1267      TERRY SL         Forwarding UK            null
1267      TERRY SL         Forwarding UK            6381JHZ

在这种情况下，第一和第三寄存器应该消失，希望我解释的都正确。

这是我的问题：

select  
idClient as "IdClient",
Company  as "Company",         
if (Department is null, "", Department) as "Department",
Numberplate,               
from      
(select
sc.idClient as idclient,
sc.businessname as company,     
d.name as department,          
v.numberplate as numberPlate,               
from entity_type et
join bigClient bc on bc.idClient=et.idClient
join smallClient sc on sc.idclient=bc.idClientAssociated
join clientVehicle cv on cv.idClientVehicle=sc.idClientVehicle
join vehicle v on v.idVehicle=cv.idVehicle    
join active_vehicle av on av.idEntityType=et.idEntityType and av.idClientVehicle=cv.idClientVehicle 
left join department d on d.idDepartment=et.idDepartment   
where bc.idClient=1234   
UNION
select 
c.idClient as idclient,
c.businessname as empresavehicle,      
cd.name as departament,      
null as matricula,          
from entity_type et
join bigClient bc on bc.idClient=et.idClient
join smallClient sc on sc.idclient=bc.idClientAssociated
join active_c ac on ac.idEntityType=et.idEntityType and ac.idBigClient=bc.idBigClient   
left join department d on d.idDepartment=et.idDepartment        
where bc.idClient=1234) t
order by company,numberplate;

Answer 1

您可以使用分析函数count，如下所示：

select * from
(select  
idClient as "IdClient",
Company  as "Company",         
if (Department is null, "", Department) as "Department",
Numberplate,
count(Numberplate) over (partition by idClient,company,department) as cnt
from
.... rest of your query ....
) t
where cnt = 0 or (cnt>0 and Numberplate is not null)

Answer 2

@Popeye解决方案应该在MySQL 8.o或更高版本中工作，对于MySQL 5.x，我可以下一步建议：

select  
idclient as "IdClient",
company  as "Company",         
if (department is null, "", department) as "Department",
group_concat(numberplate) as ,
from      
(select
sc.idClient as idclient,
sc.businessname as company,     
d.name as department,          
v.numberplate as numberplate,               
from entity_type et
join bigClient bc on bc.idClient=et.idClient
join smallClient sc on sc.idclient=bc.idClientAssociated
join clientVehicle cv on cv.idClientVehicle=sc.idClientVehicle
join vehicle v on v.idVehicle=cv.idVehicle    
join active_vehicle av on av.idEntityType=et.idEntityType and av.idClientVehicle=cv.idClientVehicle 
left join department d on d.idDepartment=et.idDepartment   
where bc.idClient=1234   
UNION
select 
c.idClient as idclient,
c.businessname as company,      
cd.name as departament,      
null as numberplate,          
from entity_type et
join bigClient bc on bc.idClient=et.idClient
join smallClient sc on sc.idclient=bc.idClientAssociated
join active_c ac on ac.idEntityType=et.idEntityType and ac.idBigClient=bc.idBigClient   
left join department d on d.idDepartment=et.idDepartment        
where bc.idClient=1234) t
group by idClient, Company, Department
order by company,numberplate;

这个查询应该有一个限制-如果不同的车牌，它会返回一个凹陷的字符串