/*
Columns1 Columns2
A1 B1
A1 B1
A1 B2
A2 B2
A2 B3
A3 B1
*/
-- Expect result data:
/*
Columns1 Columns2
A1 B1
A2 B2
A3 B1
*/
Columns1 Columns2
A1 B1
A1 B1
A1 B2
A2 B2
A2 B3
A3 B1
*/
-- Expect result data:
/*
Columns1 Columns2
A1 B1
A2 B2
A3 B1
*/
select * from tb t where columns2=(select min(columns2) from tb where columns=t.columns)两个都可以,那假如最小的col2有重复的怎么处理?是全部显示出来还是只取一个?
否则3楼的sql就行了
select Columns1, Columns2,row_number() over (partition by Columns1 order by Columns2) cnt from table )
where cnt =1
select Columns1,min(Columns2) from tb group by Columns1
select Columns1 ,min(Columns2) Columns2 from tb group by Columns1--或者是取第一个吗?
select t.* from tb t where Columns2 = (select top 1 Columns2 from tb where Columns1 = t.Columns1)
话说,当时我给他回的也是这么写的,丫说不对,让再想想~~~~~~~~我了个去&!#$%^
CREATE TABLE #TEMP(ID VARCHAR(2),ID2 VARCHAR(2))
INSERT INTO #TEMP
SELECT 'A1','B1'
UNION ALL
SELECT 'A1','B1'
UNION ALL
SELECT 'A1','B2'
UNION ALL
SELECT 'A2','B2'
UNION ALL
SELECT 'A2','B3'
UNION ALL
SELECT 'A3','B1'
SELECT DISTINCT * FROM #TEMP T0 WHERE NOT EXISTS(SELECT 1 FROM #TEMP WHERE T0.ID=ID AND T0.ID2>ID2)/*
ID ID2
A1 B1
A2 B2
A3 B1
*/