比如下面数据里价格 时间
0.4 2009-12-14 12:00:01.744
0.2 2009-12-14 12:00:01.745
1.1 2009-12-14 12:00:01.746
0.1 2009-12-14 12:00:01.748
0.1 2009-12-14 12:00:01.749
1.5 2009-12-14 12:00:01.749
1.3 2009-12-14 12:00:01.749要求查询出 价格最低而且不重复,如果都是重复的则是重复次数最少而且价格最低时间最早的那位,
也就是上面的数据查询出来后就是 0.2 2009-12-14 12:00:01.745 这条
0.4 2009-12-14 12:00:01.744
0.2 2009-12-14 12:00:01.745
1.1 2009-12-14 12:00:01.746
0.1 2009-12-14 12:00:01.748
0.1 2009-12-14 12:00:01.749
1.5 2009-12-14 12:00:01.749
1.3 2009-12-14 12:00:01.749要求查询出 价格最低而且不重复,如果都是重复的则是重复次数最少而且价格最低时间最早的那位,
也就是上面的数据查询出来后就是 0.2 2009-12-14 12:00:01.745 这条
解决方案 »
- sql2008 r2 sp2
- SQL 2008 Tcp Ip
- SQLCE的一个意外错误(频繁发生)
- 急...如何查询[用户表]中指定n个用户在[用户登录记录表]中的最新登录信息?
- 请问如何查询某一中文字符串的首字母,谢谢
- 请问我用查询分析器里输入select * into #temp1 from (select......)
- 在SQL2000中,如何设置外键关系为删除参照表主键时,响应的依赖表外键为set null.
- 用LogExplorer查看数据库备份文件中日志怎么弄
- 请问用什么ASP语句可以把 MSSQL 中的数据导入预定的 access 表中?
- SQL 分组查询问题
- 在维护计划备份中有2个选项:“跨一个或多个文件备份数据库”和“
- 存储过程中查询结果为空怎么判断?
if object_id('[tb]') is not null drop table [tb]
create table [tb]([价格] numeric(2,1),[时间] datetime)
insert [tb]
select 0.4,'2009-12-14 12:00:01.744' union all
select 0.2,'2009-12-14 12:00:01.745' union all
select 1.1,'2009-12-14 12:00:01.746' union all
select 0.1,'2009-12-14 12:00:01.748' union all
select 0.1,'2009-12-14 12:00:01.749' union all
select 1.5,'2009-12-14 12:00:01.749' union all
select 1.3,'2009-12-14 12:00:01.749'select top 1 [价格],min([时间]) as [时间]
from [tb] t
group by [价格]
order by count(1),[价格]----------------
.2 2009-12-14 12:00:01.747
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([价格] numeric(2,1),[时间] datetime)
insert [tb]
select 0.4,'2009-12-14 12:00:01.744' union all
select 0.2,'2009-12-14 12:00:01.745' union all
select 1.1,'2009-12-14 12:00:01.746' union all
select 0.1,'2009-12-14 12:00:01.748' union all
select 0.1,'2009-12-14 12:00:01.749' union all
select 1.5,'2009-12-14 12:00:01.749' union all
select 1.3,'2009-12-14 12:00:01.749'
---查询---
select
top 1 *
from
tb t
where
价格 in(select top 1 价格 from tb group by 价格 order by count(1),价格)---结果---
价格 时间
---- ------------------------------------------------------
.2 2009-12-14 12:00:01.747(所影响的行数为 1 行)
--> 测试时间:2009-12-14 18:15:19--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([价格] varchar(10),[时间] datetime)
insert [TB]
select 0.4,'2009-12-14 12:00:01.744' union all
select 0.2,'2009-12-14 12:00:01.745' union all
select 1.1,'2009-12-14 12:00:01.746' union all
select 0.1,'2009-12-14 12:00:01.748' union all
select 0.1,'2009-12-14 12:00:01.749' union all
select 1.5,'2009-12-14 12:00:01.749' union all
select 1.3,'2009-12-14 12:00:01.749'select top 1 * from [TB] t
where (select count(1) from tb where T.价格=价格 )=1
order by 价格
/*价格 时间
---------- ------------------------------------------------------
0.2 2009-12-14 12:00:01.747(所影响的行数为 1 行)*/drop table [TB]
top 1 [价格],min([时间]) as [时间]
from
[tb] t
group by
[价格]
order by
count(1),[价格]
(select min(count(1)), jiage from biao group by jiage )
(select min(count(1)), jiage from biao t1 where t.jiage = t1.jiage group by jiage )