select id,image,city,addtime from ta a order by (select count(*) from ta where city=a.city) desc
select t1.* from t1,(select id,count(1) as cs from t1 group by id) t2 where t1.id=t2.id order by t2.cs desc
select city,[sum]=count(*) from ta order by [sum] desc
select a.city , a.image , b.addtime , b.cnt from tb a, (select city , max(addtime) , count(*) as cnt addtime from tb group by city) b where a.city = b.city order by b.cnt desc
上面寫的都有問題。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 20Select 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
select tb.id,tb.city,tb.image,tb.addtime from TableName tb join (select city,Max(addtime) as addtime,count(city) as [Count] from TableName group by city) newtb on tb.addtime=newtb.addtime and tb.city=newtb.city ordery by [count] 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
-----------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
from ta a
order by (select count(*) from ta where city=a.city) desc
from ta
order by [sum] desc
(select city , max(addtime) , count(*) as cnt addtime from tb group by city) b
where a.city = b.city
order by b.cnt desc
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
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
from
TableName tb
join
(select city,Max(addtime) as addtime,count(city) as [Count] from TableName
group by city) newtb
on tb.addtime=newtb.addtime and tb.city=newtb.city
ordery by [count] desc
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
表中的列; id,image,city,addtime这个请问有谁可以?我弄了半天没实现//郁闷
那现在需要查询 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