/*
字段1 字段2 字段3 字段4 字段5 字段6 字段7
33 xxxx1 xxxx xxxx xxxx xx xx
33 xxxx2 sss xzs d sd sd
33 xxxx3 sss xzs d sd sd
44 adas1 ee dsd esf dsq dswe
44 adas2 ee dsd esf dsq dswe
44 adas3 ee dsd esf dsq dswe
.
.
.
返回
字段1 字段2 字段3 字段4 字段5 字段6 字段7
33 xxxx1 xxxx xxxx xxxx xx xx
44 adas1 ee dsd esf dsq dswe
就是以字段1为基准,返回其中的第一行信息
Select t1.*
From Table t1,
(Select 字段1,字段2=min(字段2) From Table Group By 字段1) t2
Where t1.字段1=t2.字段1
and t1.字段2=t2.字段2
Select t1.*
From Table t1
Where t1.字段2+t1.字段3+t1.字段4 in
(Select top 1 t2.字段2+t2.字段3+t2.字段4
From Table t2
Where t1.字段1=t2.字段1
order by t2.字段2+t2.字段3+t2.字段4)其中t1.字段2+t1.字段3+t1.字段4和t2.字段2+t2.字段3+t2.字段4,是把除了字段1外的所有主鍵相加或把字段2~7都相加
select f1,min(f2) f2 from tb
group by f1
) a
left join tb b on a.f1=b.f1 and a.f2=b.f2
order by a.f1,a.f2
DECLARE @T TABLE (字段1 VARCHAR(12),字段2 VARCHAR(12),字段3 VARCHAR(12),字段4 VARCHAR(12),
字段5 VARCHAR(12),字段6 VARCHAR(12),字段7 VARCHAR(12))
INSERT INTO @T
SELECT '33','xxxx1','xxxx','xxxx',' xxxx','xx','xx' UNION ALL
SELECT '33','xxxx2','sss','xzs','d','sd','sd' UNION ALL
SELECT '33','xxxx3','sss','xzs','d','sd','sd' UNION ALL
SELECT '44','adas1','ee','dsd','esf','dsq','dswe' UNION ALL
SELECT '44','adas2','ee','dsd','esf','dsq','dswe' UNION ALL
SELECT '44','adas3','ee','dsd','esf','dsq','dswe'select * from @T where 字段1 in (select distinct(字段1) from @T)
and 字段2 in (select min(字段2) from @T group by substring(字段2,1,len(字段2)-1))/* result:
33 xxxx1 xxxx xxxx xxxx xx xx
44 adas1 ee dsd esf dsq dswe
*/