22,300
社区成员




CREATE TABLE T_Test(id nvarchar(20),coun nvarchar(20),FName nvarchar(50))
INSERT INTO t_test values('1','A','刘德华');
INSERT INTO t_test values('2','B','张学友');
INSERT INTO t_test values('3','C','黎明');
INSERT INTO t_test values('4','C','黎明');
INSERT INTO t_test values('4','C','黎明');
select t.* from t_test t where id = (select min(id) from t_test where coun = t.coun and FName = t.FName) order by t.id
select t.* from t_test t where not exists (select 1 from t_test where coun = t.coun and FName = t.FName and id < t.id) order by t.id
select min(id) id , coun , fname from t_test group by fname, coun order by id
drop table t_test
/*
id coun FName
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
id coun FName
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
id coun fname
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
*/
CREATE TABLE T_Test(id nvarchar(20),coun nvarchar(20),FName nvarchar(50))
INSERT INTO t_test values('1','A','刘德华');
INSERT INTO t_test values('2','B','张学友');
INSERT INTO t_test values('3','C','黎明');
INSERT INTO t_test values('4','C','黎明');
INSERT INTO t_test values('4','C','黎明');
select t.* from t_test t where id = (select min(id) from t_test where FName = t.FName) order by t.id
select t.* from t_test t where not exists (select 1 from t_test where FName = t.FName and id < t.id) order by t.id
select min(id) id , min(coun) coun , fname from t_test group by fname order by id
drop table t_test
/*
id coun FName
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
id coun FName
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
id coun fname
-------------------- -------------------- --------------------------------------------------
1 A 刘德华
2 B 张学友
3 C 黎明
(所影响的行数为 3 行)
*/
select min(id) id,coun,fname
from T_Test
group by coun,fname