SQL Server 按某一字段分组 取 最大 (小)值所在行的数据

-- 按某一字段分组 取 最大 (小)值所在行的数据

-- (爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)

/*

数据如下：

name val memo

a    2   a2(a的第二个值)

a    1   a1--a的第一个值

a    3   a3:a的第三个值

b    1   b1--b的第一个值

b    3   b3:b的第三个值

b    2   b2b2b2b2

b    4   b4b4

b    5   b5b5b5b5b5

*/

-- 创建表并插入数据：

create

table

tb(

name

varchar

( 10 ),val

int

,memo

varchar

( 20 ))

insert

into

tb

values

(

' a '

,    2 ,  

' a2(a的第二个值) '

)

insert

into

tb

values

(

' a '

,    1 ,  

' a1--a的第一个值 '

)

insert

into

tb

values

(

' a '

,    3 ,  

' a3:a的第三个值 '

)

insert

into

tb

values

(

' b '

,    1 ,  

' b1--b的第一个值 '

)

insert

into

tb

values

(

' b '

,    3 ,  

' b3:b的第三个值 '

)

insert

into

tb

values

(

' b '

,    2 ,  

' b2b2b2b2 '

)

insert

into

tb

values

(

' b '

,    4 ,  

' b4b4 '

)

insert

into

tb

values

(

' b '

,    5 ,  

' b5b5b5b5b5 '

)

go

 

-- 一、按name分组 取 val最大 的值所在行的数据。

-- 方法1：

select

a. *

from

tb a

where

val = (

select

max

(val)

from

tb

where

name

= a.

name

)

order

by

a.

name

-- 方法2：

select

a. *

from

tb a

where

not

exists (

select

1

from

tb

where

name

= a.

name

and

val > a.val)

-- 方法3：

select

a. *

from

tb a,(

select

name

,

max

(val) val

from

tb

group

by

name

) b

where

a.

name

= b.

name

and

a.val = b.val

order

by

a.

name

-- 方法4：

select

a. *

from

tb a

inner

join

(

select

name

,

max

(val) val

from

tb

group

by

name

) b

on

a.

name

= b.

name

and

a.val = b.val

order

by

a.

name

-- 方法5

select

a. *

from

tb a

where

1 > (

select

count

( * )

from

tb

where

name

= a.

name

and

val > a.val )

order

by

a.

name

/*

name       val         memo                

---------- ----------- --------------------

a          3           a3:a的第三个值

b          5           b5b5b5b5b5

*/

 

-- 二、按name分组 取 val最小的值所在行的数据。

-- 方法1：

select

a. *

from

tb a

where

val = (

select

min

(val)

from

tb

where

name

= a.

name

)

order

by

a.

name

-- 方法2：

select

a. *

from

tb a

where

not

exists (

select

1

from

tb

where

name

= a.

name

and

val < a.val)

-- 方法3：

select

a. *

from

tb a,(

select

name

,

min

(val) val

from

tb

group

by

name

) b

where

a.

name

= b.

name

and

a.val = b.val

order

by

a.

name

-- 方法4：

select

a. *

from

tb a

inner

join

(

select

name

,

min

(val) val

from

tb

group

by

name

) b

on

a.

name

= b.

name

and

a.val = b.val

order

by

a.

name

-- 方法5

select

a. *

from

tb a

where

1 > (

select

count

( * )

from

tb

where

name

= a.

name

and

val < a.val)

order

by

a.

name

/*

name       val         memo                

---------- ----------- --------------------

a          1           a1--a的第一个值

b          1           b1--b的第一个值

*/

 

-- 三、按name分组 取 第一次出现的行所在的数据。

select

a. *

from

tb a

where

val = (

select

top

1 val

from

tb

where

name

= a.

name

)

order

by

a.

name

/*

name       val         memo                

---------- ----------- --------------------

a          2           a2(a的第二个值)

b          1           b1--b的第一个值

*/

 

-- 四、按name分组 随机取 一条数据。

select

a. *

from

tb a

where

val = (

select

top

1 val

from

tb

where

name

= a.

name

order

by

newid ())

order

by

a.

name

/*

name       val         memo                

---------- ----------- --------------------

a          1           a1--a的第一个值

b          5           b5b5b5b5b5

*/

 

-- 五、按name分组 取 最小的两个(N个)val

select

a. *

from

tb a

where

2 > (

select

count

( * )

from

tb

where

name

= a.

name

and

val < a.val )

order

by

a.

name

,a.val

select

a. *

from

tb a

where

val

in

(

select

top

2 val

from

tb

where

name

= a.

name

order

by

val)

order

by

a.

name

,a.val

select

a. *

from

tb a

where

exists (

select

count

( * )

from

tb

where

name

= a.

name

and

val < a.val

having

Count

( * ) < 2 )

order

by

a.

name

/*

name       val         memo                

---------- ----------- --------------------

a          1           a1--a的第一个值

a          2           a2(a的第二个值)

b          1           b1--b的第一个值

b          2           b2b2b2b2

*/