我有这张桌子
CREATE TABLE测试(id INT,on_off TINYINT NOT NULL); 插入测试 (id,n_off)值 (12,1), ( 13,1), (14,1),(15,0), (16,0), (17,0), (18,0) , (19,1), (20,1), (21.1), (22,1), (22,0), (23,0), (24,1), (25,1), (26,1);
如何使用mysql和php获取每个组的第一行和最后一行
min_id max_id on_off 12 14 1 19 22 1 24 26 1
或者
id on_off 12 1 14 1 20 1 22 1 24 1 26 1
您可以在MySQL中执行此操作:
set @c=0;
SELECT
if (@prev != test.on_off,@c:=@c+1,@c) as `tmp`,
min(id) as min_id,max(id) as max_id, @prev := test.on_off as on_off
from test,
(
select @prev := -1
) as i
group by `tmp`
order by test.id;
如果只需要值为1的行,则:
set @c=0;
select min_id, max_id,on_off from
(SELECT
if (@prev != test.on_off,@c:=@c+1,@c) as `tmp`,
min(id) as min_id,max(id) as max_id, @prev := test.on_off as on_off
from test,
(
select @prev := -1
) as i
group by `tmp`
order by test.id) as `t`
where on_off = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句