下面的工作完成了,但我觉得我违反了编程规则。
select *
from serverTable
where server like ‘proc1@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc2@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc3@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘pro4@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc5@server1’
order by reg_date desc
limit 1
有没有更好的方法或正确的方法来完成这件事?
最佳答案
一个选项是为每个reg_date
组找到最大server
,其中server
可以取UNION
查询中的5个值之一,然后从表中为每个server
组选择完整记录。
SELECT t1.*
FROM serverTable t1
INNER JOIN
(
SELECT server,
MAX(reg_date) AS reg_date
FROM serverTable
WHERE server in ('proc1@server1', 'proc2@server1', 'proc3@server1', 'proc4@server1', 'proc5@server1')
GROUP BY server
) t2
ON t1.server = t2.server AND
t1.reg_date = t2.reg_date