按一个字段查询最小值所在的行显示出来

[qq47772369]

测试数据如下：
就是按h01字段查询h04字段的最小值，然后把这个最小值所在的行显示出来，如果有多行，显示一行就行了。

--创建数据表Table1
CREATE TABLE [dbo].[Table1] (
[h01] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[h02] [datetime] NOT NULL ,
[h03] [int] NOT NULL ,
[h04] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[h01],
[h02],
[h03]
) ON [PRIMARY]
GO

--插入测试数据
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-10 00:00:00.000' , 2 , 15222.43 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-10 00:00:00.000' , 3 , 99164.040000000008 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 1 , 46186.309999999998 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 2 , 20046.330000000002 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 3 , 88719.600000000006 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 1 , 35707.200000000004 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 2 , 1.0 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 3 , 56563.790000000001 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 1 , 31452.029999999999 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 2 , 1.0 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 3 , 50448.050000000003 )
go

想得到结果如下
h01 h02 h03 h04
1001 2013-10-10 0:00 2 15222.43
3520 2013-10-10 0:00 2 1

[發糞塗牆]

SELECT DISTINCT *
FROM [Table1] a
WHERE EXISTS (SELECT 1 FROM (
SELECT [h01],MIN([h04])[h04] FROM [Table1] GROUP BY [h01])b WHERE a.[h01]=b.[h01] AND a.[h04]=b.h04)

[發糞塗牆]

1楼有误

SELECT *
FROM (
SELECT  *,ROW_NUMBER()OVER(PARTITION BY [h01] ORDER BY [h04])id
FROM [Table1] )a
WHERE id=1
/*
h01                                                h02                     h03         h04                    id
-------------------------------------------------- ----------------------- ----------- ---------------------- --------------------
1001                                               2013-10-10 00:00:00.000 2           15222.43               1
3520                                               2013-10-10 00:00:00.000 2           1                      1
*/

[qq47772369]

谢谢二楼的回复， 但我使用的mssql 还是2000版的，不支持函数ROW_NUMBER

[發糞塗牆]

SELECT *
FROM [Table1] a
WHERE EXISTS (SELECT  1  FROM (
SELECT [h01],MIN([h04])[h04],MIN([h02])[h02] FROM [Table1] GROUP BY [h01])b WHERE a.[h01]=b.[h01] AND a.[h04]=b.h04 AND a.[h02]=b.[h02]
)

[qq47772369]

四楼你好， 这个语句对于上面测试的数据是可以的。。。 如果存在h01,h02,h04相同,h03不同的行，则不行了。。如把上面 INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 3 , 56563.790000000001 ) 改成 INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 3 , 1) ,则不行了

[發糞塗牆]

SELECT * FROM [Table1] a WHERE EXISTS (SELECT  1  FROM ( SELECT [h01],MIN([h04])[h04],MIN([h02])[h02],min([h03])[h03] FROM [Table1] GROUP BY [h01])b WHERE a.[h01]=b.[h01] AND a.[h04]=b.h04 AND a.[h02]=b.[h02] and a.[h03]=b.[h03] ) 这样呢？

[唐诗三百首]

select h01,h02,h03,h04 
 into #t
 from Table1 a
 where not exists
 (select 1 from Table1 b
  where b.h01=a.h01 and b.h04<a.h04)    

delete a
 from #t a
 where exists
 (select 1 from #t b where b.h01=a.h01 and b.h02>a.h02)

select distinct h01,h02,h03,h04 from #t

/*
h01        h02                     h03         h04
---------- ----------------------- ----------- ----------------------
1001       2013-10-10 00:00:00.000 2           15222.43
3520       2013-10-14 00:00:00.000 2           1

(2 row(s) affected)
*/

[LongRui888]

如果你是2005的话，就简单多了。 但是在2000的话，非常麻烦，而且没办法通过一个语句来实现，下面借助一个临时来实现：

--drop table table1


--创建数据表Table1
CREATE TABLE [dbo].[Table1] (
[h01] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[h02] [datetime] NOT NULL ,
[h03] [int] NOT NULL ,
[h04] [float] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
(
[h01],
[h02],
[h03]
)  ON [PRIMARY] 
GO

--插入测试数据
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-10 00:00:00.000' , 2 , 15222.43 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-10 00:00:00.000' , 3 , 99164.040000000008 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 1 , 46186.309999999998 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 2 , 20046.330000000002 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '1001' , '2013-10-14 00:00:00.000' , 3 , 88719.600000000006 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 1 , 35707.200000000004 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 2 , 1.0 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-10 00:00:00.000' , 3 , 1)
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 1 , 31452.029999999999 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 2 , 1.0 )
INSERT [Table1] ( [h01] , [h02] , [h03] , [h04] ) VALUES ( '3520' , '2013-10-14 00:00:00.000' , 3 , 50448.050000000003 )
go


if object_id('tempdb..#table1') is not null
   drop table #table1

select identity(int,1,1) as id,*
       into #table1
from table1


select h01,h02,h03,h04
from 
(
select *,
       (select top 1 id from #table1 t2 
        where t2.h01 = t1.h01 order by h04,id) - id as rownum
from #table1 t1
)t
where t.rownum = 0
/*
h01	h02	h03	h04
1001	2013-10-10 00:00:00.000	2	15222.43
3520	2013-10-10 00:00:00.000	2	1
*/