求一句select
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
[最优解释]
SELECT A.id,A.col1,A.col2,A.col3
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
[其他解释]
SELECT MAX(ID) ID,
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)
;WITH c1(ID, 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'
)
--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
也样也可以实现你要要得啊，把你的问题在具体一点
;WITH test(ID, 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 行受影响)
*/
select max(id),col2,col3,col4 from table group by col2,col3,col4
