急求查询语句 group by有关！

[krzg2008]

表aa
id m_id m
1 01 a
2 01 b
3 02 c
4 01 d
5 02 e
6 02 f
我想要的结果是：按照m_id分组后的第一条数据，即：
1 01 a
3 02 c
分组后的第二条数据呢？
2 01 b
5 02 e
先行谢过！

[liangCK]

--> liangCK小梁 于2008-09-27
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,m_id VARCHAR(2),m VARCHAR(1))
INSERT INTO #T
SELECT '1','01','a' UNION ALL
SELECT '2','01','b' UNION ALL
SELECT '3','02','c' UNION ALL
SELECT '4','01','d' UNION ALL
SELECT '5','02','e' UNION ALL
SELECT '6','02','f'

--SQL查询如下:

SELECT *
FROM #T AS t
WHERE 1=(   --更改这个数字
         SELECT COUNT(*)
         FROM #T
         WHERE m_id=t.m_id
            AND id<=t.id
        )
        
SELECT *
FROM #T AS t
WHERE 2=(  --更改这个数字
         SELECT COUNT(*)
         FROM #T
         WHERE m_id=t.m_id
            AND id<=t.id
        )

/*
id          m_id m
----------- ---- ----
1           01   a
3           02   c

(2 行受影响)

id          m_id m
----------- ---- ----
2           01   b
5           02   e

(2 行受影响)

*/

[xabcxyz]

select * from t where id in (select Min(id) from t group by m_id)

[xabcxyz]

select * from t where id in (select Min(id) from t where id not in （select Min(id) from t group by m_id） group by m_id)

[中国风]

参照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

[ws_hgo]

kankan

[-狙击手-]

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,m_id VARCHAR(2),m VARCHAR(1))
INSERT INTO #T
SELECT '1','01','a' UNION ALL
SELECT '2','01','b' UNION ALL
SELECT '3','02','c' UNION ALL
SELECT '4','01','d' UNION ALL
SELECT '5','02','e' UNION ALL
SELECT '6','02','f'

select 
	id,
	m_id,
	m
from 
	(
	select 
		*,
		rowid = row_number() over(partition by m_id order by id)
	from #t) a
where rowid = 1
/*
id          m_id m
----------- ---- ----
1           01   a
3           02   c

(2 行受影响)
*/
select 
	id,
	m_id,
	m
from 
	(
	select 
		*,
		rowid = row_number() over(partition by m_id order by id)
	from #t) a
where rowid = 2
/*

id          m_id m
----------- ---- ----
2           01   b
5           02   e

(2 行受影响)
*/

[wer123q]

 CREATE TABLE #T (id INT,m_id VARCHAR(2),m VARCHAR(1))
INSERT INTO #T
SELECT '1','01','a' UNION ALL
SELECT '2','01','b' UNION ALL
SELECT '3','02','c' UNION ALL
SELECT '4','01','d' UNION ALL
SELECT '5','02','e' UNION ALL
SELECT '6','02','f'


select * from #t where m in
(select min(m) from #t group by m_id)
------------------------------------------
select * from #t where m in
(select min(m) from #t  where m not in (select min(m) from #t group by m_id ) group by m_id )