求一SQL语句
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 --------------------编程问答-------------------- select top 10 id,price,title,photo,city from test group by city order by price asc,time desc --------------------编程问答-------------------- 按城市分组 按价格排序 --------------------编程问答-------------------- 第一个正确给出的得满分！
select top 10 id,price,title,photo,city from test group by city order by price asc,time desc
这个是不对的，多个字段必须按照多个字段分组，这样城市就有重复的了。。 --------------------编程问答-------------------- 按城市分组 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 --------------------编程问答-------------------- 不清楚你是一共 取十条， 还是 每个城市 都取 十条 --------------------编程问答-------------------- 用存储过程似乎比较容易，取不同的(distinct)城市ID，打开游标，动态循环拼装（union all）select top 10 id,price,title,photo,city from test order by price desc这样的一段sql，存储过程返回执行拼装后的sql的查询结果集！ --------------------编程问答-------------------- 如果城市有重复的 用游标可否？ --------------------编程问答-------------------- 一共取十条
要用SQL实现。。不能用游标 --------------------编程问答--------------------
select top 10 min(price),city from test group by city,price--------------------编程问答--------------------
order by price
一共取10條，請問還需要“城市”做啥子？ --------------------编程问答--------------------
select a.id,a.price,a.title,a.photo,a.city from test a where a.price=(select min(price) from test b where a.city=b.city) order by a.price
--------------------编程问答-------------------- 就是取价格最低的前十条，要求城市不重复 --------------------编程问答-------------------- select top 10 * from(
select min(price) as price,city from tb group by city
) order by price --------------------编程问答-------------------- 是不是同一个城市的价格可能有多个？
如果这样的话，用子表查询
--------------------编程问答--------------------
这个应该可以满足吧 --------------------编程问答-------------------- 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--------------------编程问答-------------------- 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) --------------------编程问答-------------------- select top 10 min(price),city from test group by city,price
where not exists(select 1 from test b where b.city = a.city and b.price < a.price )
order by price
order by price
这个应该可以的！！！ --------------------编程问答-------------------- 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 --------------------编程问答-------------------- 15楼的就可以。 --------------------编程问答-------------------- 谢谢大家
补充：.NET技术 , ASP.NET
