按一个字段查询最小值所在的行显示出来
测试数据如下：
就是按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
想得到结果如下
h01h02 h03h04
10012013-10-10 0:00 215222.43
35202013-10-10 0:00 21
[解决办法]
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
*/
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]
)
这样呢？
[解决办法]
如果你是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
/*
h01h02h03h04
10012013-10-10 00:00:00.000215222.43
35202013-10-10 00:00:00.00021
*/
