表TEST:
col1 col2 col3
A a 1
A a 2
B a 3
B a 4
C a 5
C a 6
A b 7
A b 8
A A 9
A A 10
B B 11
D c 12
D c 13
E c 14
E c 15
说明:表中有2种数据,第一种是col1=col2,第二种是col1!=col2,其中第二种数据又分两种情况,a情况是col2相同的数据具有相同的col1,b情况是col2相同的数据具有不同的col1。现在要求把b情况的数据中,只保留一种相同的col1即可(哪种col1任意,可以取min(col1)或其他方法),即如下结果:
col1 col2 col3
A a 1
A a 2
A b 7
A b 8
A A 9
A A 10
B B 11
D c 12
D c 13
谢谢!
col1 col2 col3
A a 1
A a 2
B a 3
B a 4
C a 5
C a 6
A b 7
A b 8
A A 9
A A 10
B B 11
D c 12
D c 13
E c 14
E c 15
说明:表中有2种数据,第一种是col1=col2,第二种是col1!=col2,其中第二种数据又分两种情况,a情况是col2相同的数据具有相同的col1,b情况是col2相同的数据具有不同的col1。现在要求把b情况的数据中,只保留一种相同的col1即可(哪种col1任意,可以取min(col1)或其他方法),即如下结果:
col1 col2 col3
A a 1
A a 2
A b 7
A b 8
A A 9
A A 10
B B 11
D c 12
D c 13
谢谢!
表中有2种数据,a情况是col2相同的数据具有相同的col1,b情况是col2相同的数据具有不同的col1。现在要求把b情况的数据中,只保留一种相同的col1即可(哪种col1任意,可以取min(col1)或其他方法)
select col1,col2,col3 from test where col1<>col2
from test t1,
(
select col1,col2
from(
select col1, col2, row_number() over (partition by col2 order by col2) cnt from test t1
group by col1, col2
order by col2
) tt1
where tt1.cnt=1
) t2
where t1.col1=t2.col1 and t1.col2=t2.col2
from test t1,
(
select col1,col2
from(
select col1, col2, row_number() over (partition by col2 order by col2) cnt from test t1
group by col1, col2
order by col2
) tt1
where tt1.cnt=1
) t2
where t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col1<>t2.col2
---------------------------------------------------------------
写得有点烦,但是可以用
1.建一个区分大小写的表CREATE TABLE [test] (
[col1] [varchar] (20) COLLATE Chinese_PRC_CS_AS NULL ,
[col2] [varchar] (20) COLLATE Chinese_PRC_CS_AS NULL ,
[col3] [varchar] (20) COLLATE Chinese_PRC_CS_AS NULL
) ON [PRIMARY]
GO2.插入原始数据insert into test(col1,col2,col3)
select 'A','a','1' union all
select 'A','a','2' union all
select 'B','a','3' union all
select 'B','a','4' union all
select 'C','a','5' union all
select 'C','a','6' union all
select 'A','b','7' union all
select 'A','b','8' union all
select 'A','A','9' union all
select 'A','A','10' union all
select 'B','B','11' union all
select 'D','c','12' union all
select 'D','c','13' union all
select 'E','c','14' union all
select 'E','c','15'3.选择所需数据select test.col1,test.col2,test.col3 from test inner join
(
select min(col1) as col1, col2 from test where col1<>col2 group by col2
)t1
on test.col1<>test.col2
and test.col1=t1.col1
and test.col2=t1.col24.结果col1 col2 col3
-------------------- -------------------- --------------------
A a 1
A a 2
A b 7
A b 8
D c 12
D c 13(所影响的行数为 6 行)
完!
where t.rk<=2;