我有一个人物表
table star
starid starname
1 111
2 222
3 333
和一个照片表
table photo
photoid starid top
1 1 0
2 1 0
3 1 0
4 2 0
5 3 0
6 3 1
表star 和 表 photo 中的starid关联
我现在要做的是: 取每一个人物的一张照片出来。 如果对应starid的 top为1的话 就是置顶那么这个人放在首位。
预想结果是starid starname photoid
3 333 6
2 222 4
1 111 3请问有什么办法吗。
table star
starid starname
1 111
2 222
3 333
和一个照片表
table photo
photoid starid top
1 1 0
2 1 0
3 1 0
4 2 0
5 3 0
6 3 1
表star 和 表 photo 中的starid关联
我现在要做的是: 取每一个人物的一张照片出来。 如果对应starid的 top为1的话 就是置顶那么这个人放在首位。
预想结果是starid starname photoid
3 333 6
2 222 4
1 111 3请问有什么办法吗。
select
s.starid,s.starname,p.photoid
from
star s,photo p
where
s.starid=p.starid
and
not exists(select 1 from photo where starid=s.starid and photoid<p.photoid)
order by
p.top desc
select
s.starid,s.starname,p.photoid
from
star s,photo p
where
s.starid=p.starid
and
not exists(select 1 from photo where starid=s.starid and (top>p.top or (top=p.top and photoid>p.photoid)))
order by
p.top desc
(select top 1 photoid from photo whre photo.starid=star.starid order by photoid desc) photoid
from star
insert into @star select 1,'111'
insert into @star select 2,'222'
insert into @star select 3,'333' declare @photo table(photoid int,starid int,[top] int)
insert into @photo select 1,1,0
insert into @photo select 2,1,0
insert into @photo select 3,1,0
insert into @photo select 4,2,0
insert into @photo select 5,3,0
insert into @photo select 6,3,1 select
s.starid,s.starname,p.photoid
from
@star s,@photo p
where
s.starid=p.starid
and
not exists(select 1 from @photo where starid=s.starid and ([top]>p.[top] or ([top]=p.[top] and photoid>p.photoid)))
order by
p.[top] desc
/*
starid starname photoid
----------- ---------- -----------
3 333 6
1 111 3
2 222 4
*/
from star s join photo p on s.starid = p.starid
where not exists(select 1 from photo where starid=s.starid and photoid<p.photoid)
order by case when p.[top] = 1 then 0 else 1 end ,s.starid
(
starid int,
starname nvarchar(10)
)
insert star select 1,'111'
insert star select 2,'222'
insert star select 3,'333' create table photo
(
photoid int,
starid int,
[top] bit
)
insert photo select 1,1,0
insert photo select 2,1,0
insert photo select 3,1,0
insert photo select 4,2,0
insert photo select 5,3,0
insert photo select 6,3,1
select * ,(select top 1 photoid from photo where starid = a.starid order by [top] desc,photoid desc) photoid from star a order by starid descdrop table star
drop table photo
starid starname photoid
----------- ---------- -----------
3 333 6
2 222 4
1 111 3(3 個資料列受到影響)
insert into #star select 1,'111'
insert into #star select 2,'222'
insert into #star select 3,'333' create table #photo(photoid int,starid int,[top] int)
insert into #photo select 1,1,0
insert into #photo select 2,1,0
insert into #photo select 3,1,0
insert into #photo select 4,2,0
insert into #photo select 5,3,0
insert into #photo select 6,3,1 select S.starid,S.starname,S2.photoid from #star S join (select * from #photo p where not exists (select * from #photo where photoid>p.photoid and starid=p.starid)) S2 on S.starid=S2.starid order by [top] descstarid starname photoid
----------- ---------- -----------
3 333 6
1 111 3
2 222 4(3 行受影响)
----------- ---------- -----------
3 333 6
1 111 3
2 222 4(3 行受影响)