select * from a a1
where state=1 or
state=0 and not exists (
select 1 from a
where lid=a1.lid
and state=1
)
and not exists (
select 1 from a
where lid=a1.lid
and state=0 and id<a1.id
)
where state=1 or
state=0 and not exists (
select 1 from a
where lid=a1.lid
and state=1
)
and not exists (
select 1 from a
where lid=a1.lid
and state=0 and id<a1.id
)
(
select * from A where lid not in (select distinct lid from A where state = 1)
) m where id = (select min(id) from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) n where lid = m.lid
)
union all
select * from A where state = 1
select * from a
where id=(select top 1 id from a b where b.lid=a.lid order by state desc, id)
insert into A values(1 , 1 , 0 , '121.gif')
insert into A values(2 , 1 , 0 , '323.gif')
insert into A values(3 , 2 , 0 , '3ds.gif')
insert into A values(4 , 2 , 1 , '312.gif')
insert into A values(5 , 3 , 0 , '3312.gif')
insert into A values(6 , 4 , 0 , '113ds.gif')
insert into A values(7 , 1 , 1 , '1233ds.gif')
insert into A values(8 , 2 , 0 , '3ds31.gif')
insert into A values(9 , 3 , 0 , '3d321s.gif')
goselect m.* from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) m where id = (select min(id) from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) n where lid = m.lid
)
union all
select * from A where state = 1
order by liddrop table A/*
id lid state ltext
----------- ----------- ----------- --------------------
7 1 1 1233ds.gif
4 2 1 312.gif
5 3 0 3312.gif
6 4 0 113ds.gif(所影响的行数为 4 行)
*/
insert a select 1, 1 ,0, '121.gif'
union all select 2, 1 , 0, '323.gif'
union all select 3 , 2 , 0 , '3ds.gif'
union all select 4 , 2 , 1 , '312.gif'
union all select 5 , 3 , 0 , '3312.gif'
union all select 6 , 4 , 0 , '113ds.gif'
union all select 7 ,1 , 1 , '1233ds.gif'
union all select 8 ,2 , 0 ,'3ds31.gif'
union all select 9 ,3, 0 ,'3d321s.gif'
select * from a
where id=(select top 1 id from a b where b.lid=a.lid order by state desc, id)
/*
id lid state ltext
----------- ----------- ----- --------------------------------
4 2 1 312.gif
5 3 0 3312.gif
6 4 0 113ds.gif
7 1 1 1233ds.gif(4 row(s) affected)
*/drop table a
insert into A values(1 , 1 , 0 , '121.gif')
insert into A values(2 , 1 , 0 , '323.gif')
insert into A values(3 , 2 , 0 , '3ds.gif')
insert into A values(4 , 2 , 1 , '312.gif')
insert into A values(5 , 3 , 0 , '3312.gif')
insert into A values(6 , 4 , 0 , '113ds.gif')
insert into A values(7 , 1 , 1 , '1233ds.gif')
insert into A values(8 , 2 , 0 , '3ds31.gif')
insert into A values(9 , 3 , 0 , '3d321s.gif')
goselect m.* from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) m where id = (select min(id) from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) n where lid = m.lid
)
union all
select * from A where state = 1
order by lid
/*
id lid state ltext
----------- ----------- ----------- --------------------
7 1 1 1233ds.gif
4 2 1 312.gif
5 3 0 3312.gif
6 4 0 113ds.gif(所影响的行数为 4 行)
*/--考虑等于1时,lid也有可能重复
select m.* from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) m where id = (select min(id) from
(
select * from A where lid not in (select distinct lid from A where state = 1)
) n where lid = m.lid
)
union all
select p.* from
(
select * from A where state = 1
) p where id = (select min(id) from
(
select * from A where state = 1
) q where lid = p.lid
)
order by lid
/*
id lid state ltext
----------- ----------- ----------- --------------------
7 1 1 1233ds.gif
4 2 1 312.gif
5 3 0 3312.gif
6 4 0 113ds.gif(所影响的行数为 4 行)
*/drop table A