php - Mysql提取值分组并排序

Question

这是我的问题，我有一张 table

page_name | phase_id | type | content

Home def  | 1 |  home |<some content>
Home p2   | 2 |  home |<some content>
Rules def | 1 |  rules |<some content>
Rules p2  | 2 |  rules |<some content>
Prize def | 1 |  prize |<some content>
Contact   | 1 |  contact |<some content>

例如，我需要查询以提取具有 phase_id 值的唯一行(我必须在查询中设置 phase_id<=2)

Home p2   | 2 | home |<other content>
Rules p2  | 2 | rules |<content>
Prize def | 1 | prize |content
Contact   | 1 | contact |content

有什么想法吗？

谢谢大家

Answer 1

三种方法

drop table if exists phases;
create table phases(page_name varchar(10),  phase_id int, content varchar(20));

insert into phases values
('Home def'  , 1 , '<some content>'),
('Home p2 '  , 2 , '<other content>'),
('Rules def' , 1 , '<content>'),
('Rules p2 ' , 2 , '<content>'),
('Prize def' , 1 , 'content'),
('Contact'   , 1 , 'content');


select  s.* from
(
select  p.*,
         substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1) UniquePage,
         if(substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1)  <> @p, @rn:=1,@rn:=@rn+1) rn,
         @p:=substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1) prevp
from   (select @rn:=0,@p:='') rn,phases p
order by substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1), p.phase_id desc
) s where s.rn = 1 
;

select s.*
from 
(
select p.*,
            (Select max(p1.phase_id) from phases p1 where substring(p1.page_name,1,instr(concat(p1.page_name,' '),' ') -1) = 
            substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1)) maxpid
from phases p
) s
where s.phase_id = s.maxpid
;


select p.*
from phases p
where p.phase_id = (Select max(p1.phase_id) from phases p1 where substring(p1.page_name,1,instr(concat(p1.page_name,' '),' ') -1) = 
            substring(p.page_name,1,instr(concat(p.page_name,' '),' ') -1))
;