create table test(CustomerName varchar(20),ArriveTime varchar(10),FactTime varchar(10))
insert test select '联想集团','08:00','08:15'
union all select '康佳','09:00','09:30'
union all select '康佳','09:00','09:00'
union all select '康佳','09:00','09:20'
union all select '联想集团','08:00','08:00'
union all select 'TCL','08:30','09:00'
select CustomerName,sum(case when ArriveTime<FactTime then 1 else 0 end) as 迟到次数,sum(1) as 总次数
from test
group by CustomerName
order by CustomerName desc
create table test(CustomerName varchar(20),ArriveTime varchar(10),FactTime varchar(10))
insert test select '联想集团','08:00','08:15'
union all select '康佳','09:00','09:30'
union all select '康佳','09:00','09:00'
union all select '康佳','09:00','09:20'
union all select '联想集团','08:00','08:00'
union all select 'TCL','08:30','09:00'
select 客户名称=CustomerName,
迟到次数=sum(case when CustomerName=a.CustomerName and ArriveTime<a.FactTime then 1 else 0 end),
总次数=count(*) from test a
group by CustomerName