请能人帮写个sql
本帖最后由 kendyhj8987 于 2012-11-14 20:39:04 编辑 请能人帮写个SQL语句来：（表数据如下）
ColA ColB ColC
a 1 11
b 2 22
b 3 33
a 4 44
c 5 55
写条语句，假如ColA列有相同的只取一条记录，取最上面的一条
希望执行语句后的结果应该是：
a 1 11
b 2 22
c 5 55
[最优解释]
复制错了
WITH tb ( ColA, ColB, ColC )
AS ( SELECT 'a' ,
1 ,
11
UNION ALL
SELECT 'b' ,
2 ,
22
UNION ALL
SELECT 'b' ,
3 ,
33
UNION ALL
SELECT 'a' ,
4 ,
44
UNION ALL
SELECT 'c' ,
5 ,
55
)
SELECT *
FROM TB a
WHERE EXISTS ( SELECT 1
FROM ( SELECT colA ,
MIN(colb) colb
FROM TB
GROUP BY cola
) b
WHERE a.cola = b.cola
AND a.colb = b.colb )
SELECT * FROM TB a
WHERE EXISTS (SELECT 1 FROM
(SELECT colA,MIN(colb)t.sysTime
FROM TB
GROUP BY cola)b WHERE a.cola=b.cola AND a.colb=b.colb)
WITH tb ( ColA, ColB, ColC )
AS ( SELECT 'a' ,
1 ,
11
UNION ALL
SELECT 'b' ,
2 ,
22
UNION ALL
SELECT 'b' ,
3 ,
33
UNION ALL
SELECT 'a' ,
4 ,
44
UNION ALL
SELECT 'c' ,
5 ,
55
),
huang AS (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())id,* FROM tb)
SELECT ColA,ColB,ColC
FROM huang a
WHERE EXISTS ( SELECT 1
FROM ( SELECT MIN(id)id,colA
FROM huang
GROUP BY cola
) b
WHERE a.cola = b.cola
AND a.id = b.id )
/*
ColA ColB ColC
---- ----------- -----------
a 1 11
b 2 22
c 5 55
(3 行受影响)
*/
