方案一:缺点数据多了之后 查询速度慢,功能可以实现。 以下是 单表数据,自己可以更改分组排序
select E.ID,E.SID,E.GID,E.CID,E.ZID,E.P1,E.PHASE,E.STATE,E.TIMES FROM cws_data E WHERE(E.TIMES = (SELECT MAX(TIMES) FROM cws_data WHERE day(TIMES) =day( now())
and month(TIMES) =month( now()) and year(TIMES)=year(now()) and SID = E.SID and GID=E.GID and CID=E.CID and ZID=E.ZID and PHASE=E.PHASE ))
and E.SID=777777
group by E.GID,E.CID,E.ZID,E.PHASE
select E.ID,E.SID,E.GID,E.CID,E.ZID,E.P1,E.PHASE,E.STATE,E.TIMES FROM 表名 E WHERE(时间字段 = (SELECT MAX(时间字段) FROM 表名 WHERE day(时间字段) =day( now())
and month(时间字段) =month( now()) and year(时间字段)=year(now()) and SID = E.SID and GID=E.GID and CID=E.CID and ZID=E.ZID and PHASE=E.PHASE ))
and E.SID=777777
group by E.GID,E.CID,E.ZID,E.PHASE
**===================================================**
方案二:做法前无仅有,查询速度快。 以下是 单表数据,团队多方案测试,这个方法查询速度最快,后面的分组自己可以更改分组排序
select A.ID,A.SID,A.GID ,A.CID,A.ZID,A.P1 P1,A.PHASE PHASE,A.STATE STATE,A.TIMES from (select * from cws_data where to_days(TIMES) = to_days(now()) ) A
WHERE A.SID=777777 AND NOT EXISTS(
SELECT * FROM (select * from cws_data
where to_days(TIMES) = to_days(now()) ) B
WHERE B.GID=A.GID and B.CID=A.CID and B.ZID=A.ZID and B.PHASE=A.PHASE AND B.TIMES>A.TIMES )group by A.GID,A.CID,A.ZID,A.PHASE
ORDER BY A.GID,A.CID,A.ZID,A.PHASE ASC
=====================================================
select A.ID,A.SID,A.GID ,A.CID,A.ZID,A.P1 P1,A.PHASE PHASE,A.STATE STATE,A.TIMES from (select * from cws_data where to_days(时间字段) = to_days(now()) ) A
WHERE A.SID=777777 AND NOT EXISTS(
SELECT * FROM (select * from 表名
where to_days(时间字段) = to_days(now()) ) B
WHERE B.GID=A.GID and B.CID=A.CID and B.ZID=A.ZID and B.PHASE=A.PHASE AND B.TIMES>A.TIMES )group by A.GID,A.CID,A.ZID,A.PHASE
ORDER BY A.GID,A.CID,A.ZID,A.PHASE ASC
=====另有两套方案不写出来了,以上够用。全网没有可查当前最新时间内数据,如果有更好的方案请在评论区写上sql,谢谢!