--是不是这个意思:
--每个摄影师可以有很多记录,只需要每个摄影师的任意一条记录?
--可以再增加一个字段作为辅助,假设有一个字段是aaa
select b.* from
(select distinct photographer,max_aaa=max(aaa) from t_photoinfo group by photographer)a left join
t_photoinfo b
on a.photographer=b.photographer and a.aaa=b.aaa
--每个摄影师可以有很多记录,只需要每个摄影师的任意一条记录?
--可以再增加一个字段作为辅助,假设有一个字段是aaa
select b.* from
(select distinct photographer,max_aaa=max(aaa) from t_photoinfo group by photographer)a left join
t_photoinfo b
on a.photographer=b.photographer and a.aaa=b.aaa
create table tb1(photographer varchar(20),aaa varchar(20),bbb int,ccc datetime)
insert into tb1 select 'tom','abc',1,'2005-04-01'union
select 'jack','bcd',2,'2005-04-02'union
select 'tom','cde',3,'2005-04-03'union
select 'jack','def',4,'2005-04-04'--实现:
select b.* from
(select distinct photographer,max_aaa=max(aaa) from tb1 group by photographer)a left join
tb1 b
on a.photographer=b.photographer and a.max_aaa=b.aaa--删除表:
drop table tb1/*
photographer aaa bbb ccc
--------------- -------- ---------- -----------------------------------------
jack def 4 2005-04-04 00:00:00.000
tom cde 3 2005-04-03 00:00:00.000(所影响的行数为 2 行)
*/
具体就这句:
select b.* from
(select distinct photographer,max_aaa=max(aaa) from tb1 group by photographer)a left join
tb1 b
on a.photographer=b.photographer and a.max_aaa=b.aaa因为不知道你的t_photoinfo表的字段,你可以修改一下。
declare c cursor for select distinct photographer from t_imagesinfo where corp='P'
declare @m_photographyer char(50)
declare @t_table table([photographer] [char] (50),[imagenumber] [nvarchar] (20))
open c
fetch next from c into @m_photographyer
while @@fetch_status =0
begin
insert @t_table([photographer],[imagenumber])select top 1 photographer,imagenumber from t_imagesinfo where photographer=@m_photographyer
fetch next from c into @m_photographyer
end
close c
select * from @t_table