MySQL取每组的前N条记录：使用自连接的方式
一、对分组的记录取前N条记录：例子：取前 2条最大(小)的记录
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
1.用子查询：
SELECT
*
FROM
right2 a
WHERE
2>
(
SELECT
COUNT
(*)
FROM
right2 b
WHERE
b.id=a.id
AND
b.account>a.account)
ORDER
BY
a.id,a.account
DESC
2.用exists半连接：
SELECT
*
FROM
right2 a
WHERE
EXISTS
(
SELECT
COUNT
(*)
FROM
right2 b
WHERE
b.id=a.id
AND
a.account<b.account
HAVING
COUNT
(*)<2)
ORDER
BY
a.id,a.account
DESC
同理可以取组内最小的N条记录：
SELECT
*
FROM
right2 a
WHERE
2>
(
SELECT
COUNT
(*)
FROM
right2 b
WHERE
b.id=a.id
AND
b.account<a.account)
ORDER
BY
a.id,a.account
DESC
用exists：
SELECT
*
FROM
right2 a
WHERE
EXISTS
(
SELECT
COUNT
(*)
FROM
right2 b
WHERE
b.id=a.id
AND
a.account>b.account
HAVING
COUNT
(*)<2)
ORDER
BY
a.id,a.account
DESC
SQLServer支持
top
-N：
select
a.*
from
tb a
where
val = (
select
top
3 val
from
tb
where
name
= a.
name
)
order
by
a.
name
|
如果取每组的最大(小)一条记录我常用：
|
1
|
select
id,val
from
t b
inner
join
(
select
*
from
t a
where
order
by
val
desc
) a
on
a.id=b.id
group
by
a.id
order
by
id;
|
二.实例：取每组最大的前 N条
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
create
table
t2 (
id
int
primary
key
,
gid
char
,
col1
int
,
col2
int
) engine=innodb;
insert
into
tx01
values
(1,
'A'
,31,6),
(2,
'B'
,25,83),
(3,
'C'
,76,21),
(4,
'D'
,63,56),
(5,
'E'
,3,17),
(6,
'A'
,29,97),
(7,
'B'
,88,63),
(8,
'C'
,16,22),
(9,
'D'
,25,43),
(10,
'E'
,45,28),
(11,
'A'
,2,78),
(12,
'B'
,30,79),
(13,
'C'
,96,73),
(14,
'D'
,37,40),
(15,
'E'
,14,86),
(16,
'A'
,32,67),
(17,
'B'
,84,38),
(18,
'C'
,27,9),
(19,
'D'
,31,21),
(20,
'E'
,80,63),
(21,
'A'
,89,9),
(22,
'B'
,15,22),
(23,
'C'
,46,84),
(24,
'D'
,54,79),
(25,
'E'
,85,64),
(26,
'A'
,87,13),
(27,
'B'
,40,45),
(28,
'C'
,34,90),
(29,
'D'
,63,8),
(30,
'E'
,66,40),
(31,
'A'
,83,49),
(32,
'B'
,4,90),
(33,
'C'
,81,7),
(34,
'D'
,11,12),
(35,
'E'
,85,10),
(36,
'A'
,39,75),
(37,
'B'
,22,39),
(38,
'C'
,76,67),
(39,
'D'
,20,11),
(40,
'E'
,81,36);
create
table
tx01 (
id
int
primary
key
,
gid
char
,
col1
int
,
col2
int
) engine=innodb;
|
取每组gid 最大的前N条记录：使用自连接或则半连接：
*N=1时：
自连接：降序排好后group by取每组最大的一条。
|
1
|
select
*
from
(
select
*
from
t2
order
by
col2
desc
)
as
a
group
by
gid
order
by
gid;
|
半连接方式：找不到比最大值还大的。
|
1
|
select
*
from
t2 a
where
not
exists(
select
1
from
t2 b
where
b.gid=a.gid
and
b.col2>a.col2)
order
by
a.gid;
|
*N=3时：
自连接：
|
1
|
select
*
from
t2 a
where
3>(
select
count
(*)
from
t2
where
gid=a.gid
and
col2>a.col2)
order
by
a.gid,a.col2
desc
;
|
半连接：
|
1
|
select
*
from
t2 a
where
exists(
select
count
(*)
from
t2 b
where
b.gid=a.gid
and
a.col2<b.col2
having
(
count
(*))<3)
order
by
a.gid,a.col2
desc
|
Oracle取每组的前N条记录:可以使用分析函数，hive中也能使用
SELECT * FROM(
SELECT z.type , z.code ,ROW_NUMBER()
OVER(PARTITION BY z.type ORDER BY z.code desc) AS code_id
FROM group_info z
)
WHERE code_id <4; 取每组最大的前四条记录