怎样去除多列重复

[swdmike]

比如有一个查询返回结果：
S T M
1 2 h
1 3 k
1 2 w
1 3 q
2 3 p

我只要这样，任何S和T的相同组合我只要一个结果就可以
S T M
1 2 h
1 3 k
2 3 p

[swdmike]

我问的太简单了，如果是多列呢
S T M N X
1 2 h a d
1 3 k s d
1 2 w d j
1 3 q x t
2 3 p b e

我只要这样，任何S和T的相同组合我只要一个结果就可以
S T M N X
1 2 h a d
1 3 k s d
2 3 p b e

关键是列M，N，X的值是对应的，那应该怎么办

[yunhaiC QQ654777694]

select *
from temp group by S,T
order by S

[xmycl008]

select S,T,M from temp group by S,T,M

[ks_reny]

select S,T,max(M) M from temp group by S,T
-----or
select S,T,min(M) M from temp group by S,T

[xxyping]

select S,T,M from temp group by S,T

[sdhdy]

select s,t,m=max(m) from tb group by s,t order by s,t

select s,t,m=min(m) from tb group by s,t order by s,t

[mybelta2]

M列不相同，你要哪列呢？？？？？？？？？？？？？？？？？？？？？？？？

[mybelta2]

select s,t,max(m) as m from tb group by s,t order by s,t

select s,t,min(m) as m from tb group by s,t order by s,t

[xiequan2]

select * from tb a where not exists(select 1 from tb where s=a.s and t=a.t and m<a.m)

[htl258_Tony]

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([S] int,[T] int,[M] varchar(10))
insert [tb] select 1,2,'h'
union all select 1,3,'k'
union all select 1,2,'w'
union all select 1,3,'q'
union all select 2,3,'p'

select * from tb t where not exists(select 1 from tb where s=t.s and t=t.t and m<t.m)
/*
S           T           M
----------- ----------- ----------
1           2           h
1           3           k
2           3           p

(3 行受影响)
*/

[Liyingyue_FFS]

7楼的句子等同于select s,t,min(m) as m from tb group by s,t order by s,t

[htl258_Tony]

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([S] int,[T] int,[M] varchar(10))
insert [tb] select 1,2,'h'
union all select 1,3,'k'
union all select 1,2,'w'
union all select 1,3,'q'
union all select 2,3,'p'

select * from tb t where m in(select top 1 m from tb where s=t.s and t=t.t order by m)
/*
S           T           M
----------- ----------- ----------
1           2           h
1           3           k
2           3           p

(3 行受影响)
*/

[JonasFeng]

 

IF OBJECT_ID('LI') IS NOT NULL
   DROP TABLE LI 

CREATE TABLE LI (S INT ,T INT,M VARCHAR(10))
INSERT INTO LI (S,T,M)
SELECT 1, 2, 'h' UNION ALL
SELECT 1, 3, 'k' UNION ALL
SELECT 1, 2, 'w' UNION ALL
SELECT 1, 3, 'q' UNION ALL
SELECT 2, 3, 'p'  

--查询结果
--方法一
SELECT S,T,MIN(M) M FROM LI GROUP BY S,T  
--方法二
;WITH TLI AS 
(SELECT ROW_NUMBER() OVER(ORDER BY S,T) RID, S,T,M FROM LI )  

Select A.S,A.T,A.M from TLI A where not exists(select 1 from TLI where S=A.S  AND T =A.T and RID<A.RID)


/*结果、
  S T M
  1	2	h
  1	3	k
  2	3	p
*/

[zxdouzx]

[Quote=引用 12 楼 swdmike 的回复:]
我问的太简单了，如果是多列呢
S T M N X
1 2 h a d
1 3 k s d
1 2 w d j
1 3 q x t
2 3 p b e

我只要这样，任何S和T的相同组合我只要一个结果就可以
S T M N X
1 2 h a d
1 3 k s d
2 3 p b e

关键是列M，N，X的值是对应的，那应该怎么办
[/Quote]
这个问题应该是我们问你吧 关键是看你想要什么样的结果

[tengjian1981]

2005语法

IF OBJECT_ID('LI') IS NOT NULL
   DROP TABLE LI 

CREATE TABLE LI (S INT ,T INT,M VARCHAR(10),N VARCHAR(10),P VARCHAR(10))

INSERT INTO LI (S,T,M,N,P)
SELECT 1, 2, 'h', 'a','b' UNION ALL
SELECT 1, 3, 'k', 'b','c' UNION ALL
SELECT 1, 2, 'w', 'd','e' UNION ALL
SELECT 1, 3, 'q', 'f','g' UNION ALL
SELECT 2, 3, 'p', 'a','b'  



WITH TLI AS 
(SELECT ROW_NUMBER() OVER(ORDER BY S,T) RID, S,T,M,N,P FROM LI )  

Select A.S,A.T,A.M,A.N,A.P from TLI A where not exists(select 1 from TLI where S=A.S  AND T =A.T and RID<A.RID)

[SQL77]

SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TB  
SELECT S ,T ,M, N, X FROM #TB T1 WHERE NOT EXISTS(SELECT 1 FROM #TB WHERE T1.S=S AND T1.T=T AND T1.ID>ID)