请教各位朋友：用group by分组后，如按每组（某字段最小值）取整条记录？
请教各位朋友：用group by分组后，如何按每组（某字段最小值）取整条记录？
表M，用a分组，取每组b的最小值的整条记录（*）
a b c
1 56 kl
1 45 yui
2 89 op
2 34 uy
3 12 hj
3 65 vb
我这样不行
select a,min(b)
from M
group by a
我想取整条记录，
谢谢
[解决办法]
select * from M as t where exists(
select 1 from M where t.a=a
group by a
having min(b)=t.b
)
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-17 09:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[M]
if object_id('[M]') is not null drop table [M]
go
create table [M]([a] int,[b] int,[c] varchar(3))
insert [M]
select 1,56,'kl' union all
select 1,45,'yui' union all
select 2,89,'op' union all
select 2,34,'uy' union all
select 3,12,'hj' union all
select 3,65,'vb'
--------------开始查询--------------------------
SELECT *
FROM m a
WHERE EXISTS ( SELECT 1
FROM ( SELECT a ,
MIN(b) b
FROM [M]
GROUP BY A
) b
WHERE a.a = b.a
AND a.b = b.b )
----------------结果----------------------------
/*
(6 行受影响)
a b c
----------- ----------- ----
1 45 yui
2 34 uy
3 12 hj
(3 行受影响)
*/
