一个复杂查询

[sisiz]

表
01 aa 1
01 bb 2
02 cc 2
02 dd 2
03 ee 1
03 ff 3

查询结果
01 aa 1
02 cc 2
03 ee 1

[rucypli]

select 列1,min(列2)，min(列3）
from tb
group by 列1

[wzy_love_sly]

select * from 表 t where not exists(
select 1 from 表 where id=t.id and row_number <t.row_number
)

[百年树人]

什么规则？

select 
  col1,
  min(col2) as col2,
  min(col3) as col3
from 
  tb
group by
  col1

[ws_hgo]

select * from tb t where not exists(select * from tb where col1=t.col1 and col2>t.col2)

[jia_guijun]

select col1,col2,min(col3) from tb group by col1,col2

[wzy_love_sly]

--用name列区分大小？
declare @tb table(id varchar(10),name varchar(10),row_number int)
insert into @tb select '01','aa',1
insert into @tb select '01','bb',2
insert into @tb select '02','cc',2
insert into @tb select '02','dd',2
insert into @tb select '03','ee',1
insert into @tb select '03','ff',3



select distinct * from @tb t where not exists( 
select 1 from @tb where id=t.id and name <t.name 
)

id name row_number
01 aa 1
02 cc 2
03 ee 1

[jia_guijun]

select col1,min(col2),min(col3) from tb group by col1

[usher_gml]

select * from tb t where not exists(select 1 from tb where col1=t.col1 and col3<t.col3)

[dj3688]

select col1,min(col2),min(col3） from [table] group by col1

[dj3688]

 
select col1,min(col2),min(col3) from [table] group by col1 

[ws_hgo]

create table #TT
(
  col1 varchar(10),
  col2 varchar(20),
  col3 int
)
insert into #TT select '01','aa',1
union all select '01','bb',2
union all select '02','cc',2
union all select '02','dd',2
union all select '03','ee',1
union all select '03','ff',3

select * from #TT t where not exists(select * from #TT where col1=t.col1 and col3>t.col3)

select col2,col3
from
(
  select  *,row_number() over (order by col1) rank from #TT
) T
 where not exists(select * from 
(
 select  *,row_number() over (order by col1) rank from #TT
) tt
where col1=t.col1 and rank<t.rank)

col2                 col3
-------------------- -----------
aa                   1
cc                   2
ee                   1

(3 行受影响)

[ChinaITOldMan]

select 列1,min(列2)，min(列3）
from tb
group by 列1

[ws_hgo]

12楼的是、最标注的答案

select col1,col2,col3
from
(
  select  *,row_number() over (order by col1) rank from #TT
) T
 where not exists(select * from 
(
 select  *,row_number() over (order by col1) rank from #TT
) tt
where col1=t.col1 and rank<t.rank)

col1       col2                 col3
---------- -------------------- -----------
01         aa                   1
02         cc                   2
03         ee                   1

(3 行受影响)

[ws_hgo]

人齐结贴
全份
送上
over

[htl258_Tony]

if object_id('tb') is not null drop table tb 
 go 
create table tb (c1 varchar(10),c2 varchar(10),c3 int)
insert tb select '01','aa',1
union all select '01','bb',2
union all select '02','cc',2
union all select '02','dd',2
union all select '03','ee',1
union all select '03','ff',3
go

select * from tb t where not exists(select 1 from tb where c1=t.c1 and c2<t.c2)
/*
c1         c2         c3
---------- ---------- -----------
01         aa         1
02         cc         2
03         ee         1

(3 行受影响)
*/

这样就行了.

[SQL77]

[Quote=引用 3 楼 josy 的回复:]
什么规则？

SQL codeselect
col1,
min(col2) as col2,
min(col3) as col3
from
tb
group by
col1
[/Quote]

学习

[SQL77]

CREATE TABLE TESTMIN(NAMEID CHAR(10),NAME CHAR(10),ID INT)
INSERT TESTMIN
SELECT '01',  'aa ', 1 UNION
SELECT '01',  'bb ', 2 UNION
SELECT '02',  'cc',  2 UNION
SELECT '02',  'dd ', 2 UNION
SELECT '03',  'ee ', 1 UNION
SELECT '03',  'ff ', 3 

select * from  TESTMIN T where not exists(select 1 from TESTMIN where NAMEID=t.NAMEID and ID<=t.ID AND NAME<T.NAME)

01 aa 1
02 cc 2
03 ee 1