ID COL2 COL3 COL4
1 A A A
2 A A A
2 B B B
3 B B B
3 C C C
5 C C C
我想查询到以下结果
2 A A A
3 B B B
5 C C C
1 A A A
2 A A A
2 B B B
3 B B B
3 C C C
5 C C C
我想查询到以下结果
2 A A A
3 B B B
5 C C C
FROM T1 A JOIN T1 B ON A.id>B.id AND A.col1=B.col1 AND A.col2=B.col2 AND A.col3=B.col3
COL2,
COL3,
COL4
FROM [TABLE]
GROUP BY COL2,COL3,COL4
select *
from tb a
where a.id=(select max(id) from tb b where a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3)
AS
(SELECT 1, 'A', 'A', 'A' union all
SELECT 2, 'A', 'A', 'A' union all
SELECT 2, 'B', 'B', 'B' union all
SELECT 3, 'B', 'B', 'B' union all
SELECT 3, 'C', 'C', 'C' union all
SELECT 5, 'C', 'C', 'C'
)
--start query
SELECT
(SELECT MAX(ID) FROM c1 WHERE COL2 = d.COL2 AND COL3 = d.COL3 AND COL4 = d.COL4) id
,COL2
,COL3
,COL4
FROM c1 d
GROUP BY COL2, COL3, COL4--result
id COL2 COL3 COL4
----------- ---- ---- ----
2 A A A
3 B B B
5 C C C(3 row(s) affected)
SELECT MAX(ID) ID,COL2,COL3,COL4
FROM [TABLE]
GROUP BY COL2,COL3,COL4也样也可以实现你要要得啊,把你的问题在具体一点
AS
(
SELECT 1, 'A', 'A', 'A' union all
SELECT 2, 'A', 'A', 'A' union all
SELECT 2, 'B', 'B', 'B' union all
SELECT 3, 'B', 'B', 'B' union all
SELECT 3, 'C', 'C', 'C' union all
SELECT 5, 'C', 'C', 'C'
)
SELECT *
FROM test a
WHERE EXISTS ( SELECT 1
FROM ( SELECT MAX(id) id ,
COL2 ,
COL3 ,
COL4
FROM test
GROUP BY COL2 ,
COL3 ,
COL4
) b
WHERE a.id = b.id AND a.COL2=b.COL2 AND a.COL3=b.COL3 AND a.COL4=b.COL4)
/*
ID COL2 COL3 COL4
----------- ---- ---- ----
2 A A A
3 B B B
5 C C C
(3 行受影响)
*/