需要查询 20 个城市地区最新上传的照片各一张，按每个城市上传照片的数量降序排列
表中的列; id,image,city,addtime
------解决方案--------------------
上面寫的都有問題。
Select
A.*
From
TableName
Inner Join
(Select city, Max(addtime) As addtime, Count(*) As cityCount From TableName Group By city) B
On A.city = B.city And A.addtime = B.addtime
Order By B.cityCount Desc
------解决方案--------------------
--加上TOP 20
Select
TOP 20
A.*
From
TableName
Inner Join
(Select city, Max(addtime) As addtime, Count(*) As cityCount From TableName Group By city) B
On A.city = B.city And A.addtime = B.addtime
Order By B.cityCount Desc
------解决方案--------------------
declare @a table(id int,img image,city varchar(20),addtime datetime)
insert into @a select 1,null, 'aa ', '2001-01-01 '
union all select 2,null, 'aa ', '2001-01-02 '
union all select 3,null, 'aa ', '2001-01-03 '
union all select 4,null, 'aa ', '2001-01-04 '
union all select 5,null, 'bb ', '2001-02-01 '
union all select 6,null, 'bb ', '2001-02-02 '
union all select 7,null, 'bb ', '2001-02-03 '
union all select 8,null, 'cc ', '2001-01-02 '
union all select 9,null, 'cc ', '2001-01-03 '
union all select 10,null, 'cc ', '2001-01-05 '
union all select 11,null, 'cc ', '2001-01-06 '
union all select 12,null, 'cc ', '2001-01-07 '
select * from @a
select a.* from @a a inner join (select city,max(addtime) MaxDate,count(*) mycount from @a group by city) tem on a.city = tem.city and a.addtime = tem.MaxDate order by mycount desc
------解决方案--------------------
whui48() ( ) 信誉：100 Blog 加为好友 2007-04-10 09:59:31 得分: 0
那现在需要查询 20 个城市地区最新上传的照片各一张，按每个城市一周（星期一到星期日）上传照片的数量降序排列
表中的列; id,image,city,addtime
这个请问有谁可以？我弄了半天没实现//。。。郁闷。。。
-----------
try
Select
TOP 20
A.*
From
TableName
Inner Join
(Select city, Max(addtime) As addtime, Count(*) As cityCount From TableName Where DateDiff(wk, addtime, GetDate()) = 0 Group By city) B
On A.city = B.city And A.addtime = B.addtime
Order By B.cityCount Desc