数据原型:
两个节点,上报数据;节点通过网关接入,中间更换了网关(netmac1-->netmac2)
dataid,netmac,nodemac,updatetime,data1,data2
1 net1 node1 1 1 1
2 net1 node2 2 1 1
3 net1 node1 3 1 1
4 net1 node2 4 1 1
5 net2 node1 5 1 1
6 net2 node2 6 1 1
7 net2 node1 7 1 1
8 net2 node2 8 1 1我希望统计的结果是:统计每个节点最新的一次数据。
7 net2 node1 7 1 1
8 net2 node2 8 1 1
但是查询语句导致netmac随机,有可能是net1,或者net2,这样:
7 net1 node1 7 1 1
8 net2 node2 8 1 1
两个节点,上报数据;节点通过网关接入,中间更换了网关(netmac1-->netmac2)
dataid,netmac,nodemac,updatetime,data1,data2
1 net1 node1 1 1 1
2 net1 node2 2 1 1
3 net1 node1 3 1 1
4 net1 node2 4 1 1
5 net2 node1 5 1 1
6 net2 node2 6 1 1
7 net2 node1 7 1 1
8 net2 node2 8 1 1我希望统计的结果是:统计每个节点最新的一次数据。
7 net2 node1 7 1 1
8 net2 node2 8 1 1
但是查询语句导致netmac随机,有可能是net1,或者net2,这样:
7 net1 node1 7 1 1
8 net2 node2 8 1 1
select max(data.id) id,data.net_mac,data.node_mac, max(node.name) node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ",") as temperature,
group_concat(data.humidity order by up_date desc separator ",") as humidity,
group_concat(data.pressure order by up_date desc separator ",") as pressure,
group_concat(data.illumination order by up_date desc separator ",") as illumination
from t_data data left join t_node node on node.mac = data.node_mac
where 1=1
group by node_mac
order by node_name ASC
(select nodemac,max(updatetime) updatetime from t_data group by nodemac)t
left join t_data data
on data.nodemac=t.nodemac and data.updatetime=t.updatetime
(标绿色,并加 ↓</span>)
(标绿色,并加 ↓</span>)
select data.* from
(select nodemac,updatetime,row_number()over(partition by nodemac order by updatetime) tid from t_data )t
left join t_data data
on tid<=2 and data.nodemac=t.nodemac and data.updatetime=t.updatetime
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by node_mac order by up_date) tid from t_data) t
left join t_data dat' at line 2 select data.* from
(select node_mac,up_date,row_number()over(partition by node_mac order by up_date) tid from t_data) t
left join t_data data
on tid<=2 and data.node_mac=t.node_mac and data.up_date=t.up_date
(select nodemac,updatetime,row_number()over(partition by nodemac order by updatetime) tid from t_data )t
left join t_data data
on data.nodemac=t.nodemac and data.updatetime=t.updatetime
where tid<=2
上面tid<=2 应该放在where 中
我现在的实现方法是:先查出每个数据的最新一系列值,然后用json显示最新的一个值。如下面这一句:
group_concat(data.temperature order by up_date desc separator ",") as temperature,
(
select nodemac,updatetime,
(select count(*) from t_data b where b. nodemac=a.nodemac and b.updatetime<=a.updatetime) row
from t_data a
)
where row<=2
(select * from
(
select nodemac,updatetime,
(select count(*) from t_data b where b. nodemac=a.nodemac and b.updatetime<=a.updatetime) row
from t_data a
)c
where row<=2
)t
left join
t_data d
on d.nodemac=t.nodemac and d.updatetime=t.updatetime
(netmac,updatetime,)要建组合索引
另:你的结果行大约多少?
;with t as
(select pid,MAX(id)id from a group by pid)
select * from t
union
select a.pid,MAX(a.id) from a join t on a.pid=t.pid and a.id<t.id group by a.pid
不支持就这样
select pid,MAX(id)id from a group by pid
union
select a.pid,MAX(a.id) from a
join (select pid,MAX(id)id from a group by pid)t
on a.pid=t.pid and a.id<t.id group by a.pid
id换成updatetime
还有表名