/**/
/*--------------------- 建立测试表-----------------------------------------------------------------------------------*/
CREATE TABLE [ tx ] (
[ name ] [ char ] ( 2 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ num ] [ int ] NULL ,
[ col ] [ char ] ( 2 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
/**/ /*--------------------插入记录-------------------------------------------------------------------------------------------*/
insert tx
select ' z ' , 1 , ' z1 ' union all
select ' z ' , 2 , ' z2 ' union all
select ' x ' , 1 , ' x1 ' union all
select ' x ' , 2 , ' x2 ' union all
select ' z ' , 2 , ' z1 ' union all
select ' z ' , 1 , ' z2 ' union all
go
/**/ /*--------------------显示出以 以name分组下,num 最大或最小的记录集----------------------------*/
select a. *
from tx a ,( select name, max (num) as num from tx group by name) as b -- --------一个表和一个表的条件子集匹配
where a.name = b.name and a.num = b.num -- ------以上二者的条件
-- -----------------------------------------------------------
1 ,num可变成比较大小的任意数据类型如: datetime
CREATE TABLE [ tx ] (
[ name ] [ char ] ( 2 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ num ] [ int ] NULL ,
[ col ] [ char ] ( 2 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
/**/ /*--------------------插入记录-------------------------------------------------------------------------------------------*/
insert tx
select ' z ' , 1 , ' z1 ' union all
select ' z ' , 2 , ' z2 ' union all
select ' x ' , 1 , ' x1 ' union all
select ' x ' , 2 , ' x2 ' union all
select ' z ' , 2 , ' z1 ' union all
select ' z ' , 1 , ' z2 ' union all
go
/**/ /*--------------------显示出以 以name分组下,num 最大或最小的记录集----------------------------*/
select a. *
from tx a ,( select name, max (num) as num from tx group by name) as b -- --------一个表和一个表的条件子集匹配
where a.name = b.name and a.num = b.num -- ------以上二者的条件
-- -----------------------------------------------------------
1 ,num可变成比较大小的任意数据类型如: datetime
朋友的方法:
declare
@t
table
(name
varchar
(
10
),num
int
,col
varchar
(
10
))
insert into @t select ' z ' , 1 , ' z1 '
union all select ' z ' , 2 , ' z2 '
union all select ' x ' , 3 , ' x1 '
union all select ' x ' , 9 , ' x2 '
union all select ' e ' , 4 , ' e1 '
union all select ' e ' , 15 , ' e2 '
select *
from @t a
where exists -- ------exists的应用
( select 1 from @t where a.name = name group by name having a.num = max (num)) -- -------having a.num=max(num),只找出分组中最大num记录
insert into @t select ' z ' , 1 , ' z1 '
union all select ' z ' , 2 , ' z2 '
union all select ' x ' , 3 , ' x1 '
union all select ' x ' , 9 , ' x2 '
union all select ' e ' , 4 , ' e1 '
union all select ' e ' , 15 , ' e2 '
select *
from @t a
where exists -- ------exists的应用
( select 1 from @t where a.name = name group by name having a.num = max (num)) -- -------having a.num=max(num),只找出分组中最大num记录