對表中數(shù)據(jù)分組，有時只需要某列的聚合值；有時卻需要返回整行數(shù)據(jù)，常用的方法有：子查詢、ROW_NUMBER、APPLY，總體感覺還是ROW_NUMBER比較直觀。
測試數(shù)據(jù)：
if
OBJECT_ID
(
'
testGroup
'
)
is
not
null
drop
table
testGroup
GO
create
table
testGroup(ID
int
identity
primary
key
,UserID
int
,OrderID
int
)
GO
insert
testGroup
select
1
,
10
union
all
select
1
,
20
union
all
select
1
,
30
union
all
select
2
,
100
union
all
select
2
,
200
union
all
select
3
,
1000
union
all
select
3
,
2000
union
all
select
3
,
3000
union
all
select
3
,
4000
?
一. 取分組中第1行(最大/最小值)
1. 取出分組中某列最大/最小值，不要求顯示其他列
最常見的分組聚合，用group by 分組時，只有參加分組/聚合的列才可以被顯示。
select
UserID,
MAX
(OrderID)
as
MaxOrderID
from
testGroup
group
by
UserID
? 2. 取出分組中某列最大/最小值，要求顯示其他列
要顯示表中其他列，用group by 不好實(shí)現(xiàn)，可以借助子查詢。
select
*
from
testGroup a
where
ID
=
(
select
MAX
(ID)
from
testGroup b
where
a.UserID
=
b.UserID)
order
by
ID
--
或者
select
*
from
testGroup
where
ID
in
(
select
MAX
(ID)
from
testGroup
group
by
UserID)
--
或者
select
*
from
testGroup
as
a
where
a.ID
in
(
select
top
1
ID
from
testGroup b
where
a.UserID
=
b.UserID
order
by
b.OrderID
desc
)
--
或者
select
*
from
testGroup a
where
not
exists
(
select
1
from
testGroup b
where
a.UserID
=
b.UserID
and
a.OrderID
<
b.OrderID)
--
或者
select
*
from
testGroup a
where
(
select
count
(
1
)
from
testGroup b
where
a.UserID
=
b.UserID
and
a.id
<=
b.id)
=
1
?
二. 取分組中前N行(排名前幾名)
前N行為正向排序(ASC)，后N行改為反向排序(DESC)即可，N=1時也就是取最大/最小值的行。下面以前2名(N=2)為例。
1. SQL Server 2000的寫法
(1)子查詢
select
*
from
testGroup
as
a
where
a.ID
in
(
select
top
2
ID
from
testGroup b
where
a.UserID
=
b.UserID
order
by
b.OrderID)
--
或者
select
*
from
testGroup a
where
not
exists
(
select
1
from
testGroup b
where
a.UserID
=
b.UserID
and
a.OrderID
>
b.OrderID
having
count
(
1
)
>=
2
)
--
或者
select
*
from
testGroup a
where
(
select
count
(
1
)
from
testGroup b
where
a.UserID
=
b.UserID
and
a.ID
>=
b.ID)
<=
2
--
沒有唯一標(biāo)識的表，可以用checksum來標(biāo)識每行
select
*
from
testGroup
as
a
where
checksum(
*
)
in
(
select
top
2
checksum(
*
)
from
testGroup b
where
a.UserID
=
b.UserID
order
by
b.OrderID)
? 2. SQL Server 2005新語法
(2) ROW_NUMBER()
select
ID, UserID, OrderID
from
(
select
*
, ROW_NUMBER()
over
(partition
by
UserID
order
by
OrderID) num
from
testGroup ) t
where
t.num
between
1
and
2
?(3) APPLY(TOP)
select
distinct
t.
*
from
testGroup a
cross
apply (
select
top
2
ID, UserID, OrderID
from
testGroup b
where
a.UserID
=
b.UserID
order
by
b.OrderID)
as
t
?
三. 取分組中第N行(排名第N名)
把上面的查詢中，范圍值都改為固定值，就可以取具體某一行了，下面以第3名(N=3)為例。
(1) 子查詢
select
*
from
testGroup a
where
(
select
count
(
1
)
from
testGroup b
where
a.UserID
=
b.UserID
and
a.OrderID
>=
b.OrderID)
=
3
--
或者
select
*
from
testGroup a
where
exists
(
select
1
from
testGroup b
where
a.UserID
=
b.UserID
and
a.OrderID
>=
b.OrderID
having
count
(
1
)
=
3
)
?(2) ROW_NUMBER()
select
ID, UserID, OrderID
from
(
select
*
, ROW_NUMBER()
over
(partition
by
UserID
order
by
OrderID) num
from
testGroup ) t
where
t.num
=
3
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
