create table test(PhotoID int,ShopID int,PhotoName varchar(50),PhotoDefault int,AddDate datetime)
insert test select 8,76,'4164022546.jpg',0,'2007-06-04 16:40:00'
union all select 9,53,'4164215171.jpg',0,'2007-06-04 16:42:00'union all select 10,53,'4164612343.jpg',1,'2007-06-04 16:46:00'
union all select 11,53,'4164655671.jpg',0,'2007-06-04 16:47:00'
union all select 12,53,'417650828.jpg',0,'2007-06-04 17:07:00'
union all select 13,3,'4223634562.jpg',0,'2007-06-04 22:37:00'
union all select 14,76,'422395162.jpg',0,'2007-06-04 22:40:00'select * into #t from
(
select * from test a where not exists
(
select 1 from test where ShopID=a.ShopID and AddDate>a.AddDate
) and PhotoDefault=0 or PhotoDefault=1
)bselect * from #t a where not exists
(
select 1 from #t where ShopID=a.ShopID and PhotoDefault>a.PhotoDefault
)
drop table test,#t
insert test select 8,76,'4164022546.jpg',0,'2007-06-04 16:40:00'
union all select 9,53,'4164215171.jpg',0,'2007-06-04 16:42:00'union all select 10,53,'4164612343.jpg',1,'2007-06-04 16:46:00'
union all select 11,53,'4164655671.jpg',0,'2007-06-04 16:47:00'
union all select 12,53,'417650828.jpg',0,'2007-06-04 17:07:00'
union all select 13,3,'4223634562.jpg',0,'2007-06-04 22:37:00'
union all select 14,76,'422395162.jpg',0,'2007-06-04 22:40:00'select * into #t from
(
select * from test a where not exists
(
select 1 from test where ShopID=a.ShopID and AddDate>a.AddDate
) and PhotoDefault=0 or PhotoDefault=1
)bselect * from #t a where not exists
(
select 1 from #t where ShopID=a.ShopID and PhotoDefault>a.PhotoDefault
)
drop table test,#t
13 3 4223634562.jpg 0 2007-06-04 22:37:00.000
14 76 422395162.jpg 0 2007-06-04 22:40:00.000
(select top 1 PhotoID from # b where b.ShopID=a.ShopID
order by case PhotoDefault when 1 then 0 else 1 end,AddDate desc)
from # a group by ShopID)
select * from
(
select * from test a where not exists
(
select 1 from test where ShopID=a.ShopID and AddDate>a.AddDate
) or PhotoDefault=1
) a
where not exists
(
select 1 from
(
select * from test a where not exists
(
select 1 from test where ShopID=a.ShopID and AddDate>a.AddDate
) or PhotoDefault=1
)b where ShopID=a.ShopID and PhotoDefault>a.PhotoDefault
)
如果PhotoDefault为0的话 = AddDate最大值PhotoDefault为1的记录 是哪个字段?,如果同一条查询语句,得出的字段都不同那你程序怎么用
insert into test
select 8 , 76 ,' 4164022546.jpg', 0 , '2007-06-04 16:40:00' union all
select 9 , 53 ,' 4164215171.jpg', 0 , '2007-06-04 16:42:00' union all
select 10 , 53 ,' 4164612343.jpg', 1 , '2007-06-04 16:46:00' union all
select 11 , 53 ,' 4164655671.jpg', 0 , '2007-06-04 16:47:00' union all
select 12 , 53 ,' 417650828.jpg ', 0 , '2007-06-04 17:07:00' union all
select 13 , 3 ,' 4223634562.jpg ', 0 , '2007-06-04 22:37:00' union all
select 14 , 76 ,' 422395162.jpg ', 0 , '2007-06-04 22:40:00'select * from test t1,
(
select ShopID,isnull(time1,time2) time0 from
(select ShopID,max(case when PhotoDefault=1 then AddDate end) time1,MAX(AddDate) time2
from test
group by ShopID
) t
)t2
where t1.ShopID=t2.ShopID and t1.AddDate=t2.time0