如一张表有两列，字段A,B。
A B
1 a
1 b
1 c
1 d
1 f
2 g
2 h
2 i
2 j
2 k
想得到
A B
1 a
2 g
的结果，
即"查询分组中每组第一条记录组成的表 "。
[code="SQL"]b列是有序的吗？
select A,MIN(B) from table1 group by A
[/code]
先去掉B列分组，并在select的列中加入包含B列的表top 1 查询如下语句
[code="sql"]select a, (select top 1 B from table2 where table1.col_1 = table2.col_2) from table1 group by [/code]
先排序再分组
select t.A,t.B from (select * from test order by B desc) t group by A
设表结构:t( a, b)
[code="sql"]
create table t(
a char(1) not null ,
b char(1) not null)
engine = innodb default charset=utf8;
[/code]
[code="sql"]
insert into t values
('A','B'), ('1','a'),('1','b'),('1','c'),('1','d'),('1','f'),
('2','g'),('2','h'),('2','i'),('2','j'),('2','k');
[/code]
method 1
[code="sql"]
select t1.a ,
(select b from t t2 where t2.a=t1.a limit 1)
from (select distinct a from t) t1
[/code]
method 2
[code="sql"]
select distinct t1.a ,
(select b from t t2 where t2.a=t1.a limit 1)
from t t1
[/code]
方法 3:这个不一定适合你不过是一种思路
[code="sql"]
select
t1.a , substring(group_concat(distinct t2.b order by t1.a asc),1,1)
from t t1
inner join t t2 on t1.a=t2.a
group by t1.a
order by null
[/code]