--
先建立一个测试表:
CREATE TABLE [ test ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ a ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ b ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ c ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' b ' , ' c ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' b ' , ' cc ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' aa ' , ' bb ' , ' c ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' aa ' , ' bb ' , ' cc ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' bb ' , ' ccc ' )
-- 查询语句
select id,a,b,c from test a where
id = ( SELECT min (id) FROM test b where a.a = b.a and a.b = b.b) order by id
结果:
CREATE TABLE [ test ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ a ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ b ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ c ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' b ' , ' c ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' b ' , ' cc ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' aa ' , ' bb ' , ' c ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' aa ' , ' bb ' , ' cc ' )
INSERT INTO [ MisDB ] . [ dbo ] . [ test ] ( [ a ] , [ b ] , [ c ] ) VALUES ( ' a ' , ' bb ' , ' ccc ' )
-- 查询语句
select id,a,b,c from test a where
id = ( SELECT min (id) FROM test b where a.a = b.a and a.b = b.b) order by id
1 a b c
3 aa bb c
5 a bb ccc