分组后取最大值(mysql)

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


(爱新觉罗.毓华 
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

*/



--
六、按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 
desc
) 
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          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5

*/


--
七，如果整行数据有重复，所有的列都相同。

/*

数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5

*/


--
在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。

--
创建表并插入数据：


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
'
,    
1
,   
'
a1--a的第一个值
'
)

insert
 
into
 tb 
values
(
'
a
'
,    
3
,   
'
a3: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



select
 
*
 , px 
=
 
identity
(
int
,
1
,
1
) 
into
 tmp 
from
 tb


select
 m.name,m.val,m.memo 
from

(
  
select
 t.
*
 
from
 tmp t 
where
 val 
=
 (
select
 
min
(val) 
from
 tmp 
where
 name 
=
 t.name)
) m 
where
 px 
=
 (
select
 
min
(px) 
from

(
  
select
 t.
*
 
from
 tmp t 
where
 val 
=
 (
select
 
min
(val) 
from
 tmp 
where
 name 
=
 t.name)
) n 
where
 n.name 
=
 m.name)


drop
 
table
 tb,tmp


/*

name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

(2 行受影响)

*/


--
在sql server 2005中可以使用row_number函数，不需要使用临时表。

--
创建表并插入数据：


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
'
,    
1
,   
'
a1--a的第一个值
'
)

insert
 
into
 tb 
values
(
'
a
'
,    
3
,   
'
a3: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



select
 m.name,m.val,m.memo 
from

(
  
select
 
*
 , px 
=
 row_number() 
over
(
order
 
by
 name , val) 
from
 tb
) m 
where
 px 
=
 (
select
 
min
(px) 
from

(
  
select
 
*
 , px 
=
 row_number() 
over
(
order
 
by
 name , val) 
from
 tb
) n 
where
 n.name 
=
 m.name)


drop
 
table
 tb


/*

name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

(2 行受影响)

*/