求一SQL语句

[wdx2010]

select top 10 id,price,title,photo,city from test
要求取出不同城市价格最低的最新10条记录

伪SQL:
select top 10 id,price,title,photo,city from test group by city order by price desc

[wdx2010]

就是取价格最低的前十条，要求城市不重复

[takako_mu]

select top 10 * from(
select min(price) as price,city from tb group by city
) order by price

[v风雪山神庙v]

是不是同一个城市的价格可能有多个？
如果这样的话，用子表查询

[PitTroll]

[Quote=引用 15 楼 takako_mu 的回复:]
select top 10 * from(
select min(price) as price,city from tb group by city
) order by price
[/Quote]
这个应该可以满足吧

[J_HongHai]

select top 10 min(price),city from test group by city order by price,time desc

[打一壶酱油]

15楼的可以，我的也可以

select top 10 id,price,title,photo,city from test a
where not exists(select 1 from test b where b.city = a.city and b.price <  a.price )
order by price desc

[打一壶酱油]

前面搞错了 排序

select top 10 id,price,title,photo,city from test a
where not exists(select 1 from test b where b.city = a.city and b.price <  a.price )
order by price 

[xf2711]

select top 10 id,price,title,photo,city from test _out where price = (select min(price) from test where city=_out.city) and not exists(select 1 from test where city=_out.city and price=_out.price and id > _out.id)

[wangderong8389945]

select top 10 min(price),city from test group by city,price
order by price
这个应该可以的！！！

[xf2711]

select top 10 id,price,title,photo,city from test _out and not exists(select 1 from test where city=_out.city and price<_out.price and id > _out.id) order by price asc

[lilianjie0426]

15楼的就可以。

[wdx2010]

谢谢大家

[q107770540]

select top 10 id,price,title,photo,city from test group by city order by price asc,time desc

[一切为了你]

按城市分组 按价格排序

[wdx2010]

第一个正确给出的得满分！

select top 10 id,price,title,photo,city from test group by city order by price asc,time desc
这个是不对的，多个字段必须按照多个字段分组，这样城市就有重复的了。。

[wdx2010]

按城市分组 city in(..)这样也不行，city换是有重复的
select max(id) from test group city 也不行，这样得到的不是最低价格的id
select min(price) from test group city 也不行..

以前写过忘记了，貌似很麻烦啊

[仙儿]

select top 10 max(id),min(price),city from test group by city order by price asc,time desc

[打一壶酱油]

不清楚你是一共 取十条， 还是 每个城市 都取 十条

[dicklee1214]

用存储过程似乎比较容易，取不同的(distinct)城市ID，打开游标，动态循环拼装（union all）select top 10 id,price,title,photo,city from test order by price desc这样的一段sql，存储过程返回执行拼装后的sql的查询结果集！

[子夜__]

如果城市有重复的 用游标可否？