实现这个功能的查询语句应该怎么写???
PhotoID ShopID PhotoName PhotoDefault AddDate
----------- ----------- ---------------- ------------ --------------------
8 76 4164022546.jpg 0 2007-06-04 16:40:00
9 53 4164215171.jpg 0 2007-06-04 16:42:00
10 53 4164612343.jpg 1 2007-06-04 16:46:00
11 53 4164655671.jpg 0 2007-06-04 16:47:00
12 53 417650828.jpg 0 2007-06-04 17:07:00
13 3 4223634562.jpg 0 2007-06-04 22:37:00
14 76 422395162.jpg 0 2007-06-04 22:40:00
我要的结果是如果PhotoDefault为1的话，那么ShopID重复的记录就显示这条PhotoDefault为1的记录，如果PhotoDefault为0的话那么就取AddDate最大值。这个句是作为其它语句的子查询用的，所以不能用ORDER BY!
结果应该是这样的：
PhotoID ShopID PhotoName PhotoDefault AddDate
----------- ----------- ---------------- ------------ --------------------
10 53 4164612343.jpg 1 2007-06-04 16:46:00
13 3 4223634562.jpg 0 2007-06-04 22:37:00
14 76 422395162.jpg 0 2007-06-04 22:40:00
这条语句应该怎么写？
[解决办法]
select * from # where photoid in (select
(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
)
