sql中计算输出百分比 ,并保留两位小数或判断数据库中时间，是否为当日时间进行判断，输出百分比，当前在线数，今日总在线数

 

#状态为1占比数(状态分为0和1)

decimal(10,0)：保留小数0  例如：输出80

decimal(10,2)：保留小数2  例如：输出80.66

CONCAT(80,'%')  例如：输出80%

select CONCAT(convert(sum(`status`=1)*100/count(*),decimal(10,0)),'%') as 百分比 from equipment

#判断数据库时间与当前系统时间对比筛选输出百分比（年月日对比）

#今日在线/今日总在线(百分比)

select CONCAT(convert(sum(`status`=1)*100/count(*),decimal(10,0)),'%') as 百分比 from equipment  where date_format(create_time,'%Y-%m-%d')= date_format(now(),'%Y-%m-%d')

#当前在线人数(判断时间和状态，时间精确到分)

select count(*) from equipment where equipment.`status` = 1 and date_format(equipment.create_time,'%Y-%m-%d %H:%i')= date_format(now(),'%Y-%m-%d %H:%i')

#今日总在线数(判断时间，无视状态)

select count(*) from equipment where equipment.`status` in(0,1) and date_format(equipment.create_time,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d');

#今日在线/总数(百分比)

select CONCAT(convert(sum(`status`=1)*100/(SELECT count(*) FROM equipment),decimal(10,0)),'%') as 百分比 from equipment  where date_format(create_time,'%Y-%m-%d')= date_format(now(),'%Y-%m-%d')

注意：

#年月日,时分秒
'%Y-%m-%d %H:%i:%s'

子查询问题：(注意：DISTINCT的使用)

select DISTINCT(user_id),voltage,electricity from va where create_time in (select max(create_time)from va group by user_id) ;

如有个别重复user_id处理方式：（问题原因待总结）

select DISTINCT(user_id),voltage,electricity from va where (create_time,user_id) in (select max(create_time),user_id from va group by user_id)

问题：

select user_id,voltage,electricity from va where create_time in (select max(create_time)from va group by user_id) ;

左连接+子查询(注意：DISTINCT的使用)

select DISTINCT(equipment.id),equipment.equip_name,equipment.`status`,equipment.update_time,equipment.ip_site,equipment.lng,equipment.lat,equipment.address,va.voltage,va.electricity FROM equipment LEFT JOIN va on equipment.user_id=va.user_id WHERE (equipment.user_id,va.voltage,va.electricity) in (select DISTINCT(user_id),voltage,electricity from va where create_time in (select max(create_time)from va group by user_id));

如有个别重复id处理方式：（问题原因待总结）

select DISTINCT(equipment.id),equipment.equip_name,equipment.`status`,equipment.update_time,equipment.ip_site,equipment.lng,equipment.lat,equipment.address,va.voltage,va.electricity FROM equipment LEFT JOIN va on equipment.user_id=va.user_id WHERE (equipment.user_id,va.voltage,va.electricity) in (select DISTINCT(user_id),voltage,electricity from va where (create_time,user_id) in (select max(create_time),user_id from va group by user_id));