表A
a
b
c
表B
a name1
a name2
a name3
b name1
b name2
b name3
c name1
c name2
c name3
想得到的结果:(表B中每种类型的第一条)
a name1
b name1
c name1
a
b
c
表B
a name1
a name2
a name3
b name1
b name2
b name3
c name1
c name2
c name3
想得到的结果:(表B中每种类型的第一条)
a name1
b name1
c name1
from tablename group by column1
FROM B
GROUP BY col1
ORDER BY col1
insert B select 'a', 'name1'
union all select 'a', 'name2'
union all select 'a', 'name3'
union all select 'b', 'name1'
union all select 'b', 'name2'
union all select 'b', 'name3'
union all select 'c', 'name1'
union all select 'c', 'name2'
union all select 'c', 'name3'select col1, col2=min(col2) from B group by col1--result
col1 col2
---------- ----------
a name1
b name1
c name1(3 row(s) affected)
insert B select 'a', 'name1'
union all select 'a', 'name2'
union all select 'a', 'name3'
union all select 'b', 'name1'
union all select 'b', 'name2'
union all select 'b', 'name3'
union all select 'c', 'name1'
union all select 'c', 'name2'
union all select 'c', 'name3'select * from B tmp
where (select count(*) from B where col1=tmp.col1 and col2<=tmp.col2)=1--result
col1 col2
---------- ----------
a name1
b name1
c name1(3 row(s) affected)
union all
SELECT TOP 1 col1,B.col2 FROM B WHERE col1='b'
union all
SELECT TOP 1 col1,B.col2 FROM B WHERE col1='c'