比如
A B
987654 啊啊
012345 啊啊
123123 啊啊
123123 宝贝
123456 尺寸
123456 味道
123456 低调
654321 方法
654321 额额
456789 前期
想要结果
123123 啊啊
123123 宝贝
123456 尺寸
123456 味道
123456 低调
654321 方法
654321 额额
用 as with命令怎么能实现
或者还有其他办法没有
------解决思路----------------------
请问原表中"456789 前期"这行, 为何在结果中木有了?
------解决思路----------------------
create table es
(A varchar(10),B varchar(10))
insert into es
select '987654','啊啊' union all
select '012345','啊啊' union all
select '123123','啊啊' union all
select '123123','宝贝' union all
select '123456','尺寸' union all
select '123456','味道' union all
select '123456','低调' union all
select '654321','方法' union all
select '654321','额额' union all
select '456789','前期'
with t as
(select row_number() over(order by getdate()) 'rn',A,B
from es)
select distinct x.A,x.B
from t x
where exists(select 1 from t y where y.rn<>x.rn and y.A=x.A)
/*
A B
---------- ----------
123123 啊啊
123123 宝贝
123456 尺寸
123456 低调
123456 味道
654321 额额
654321 方法
(7 row(s) affected)
*/
------解决思路----------------------
use master
go
if OBJECT_ID('a') is not null drop table a
go
create table a(a varchar(10),b varchar(10))
go
insert into a
select '987654','啊啊' union all
select '012345','啊啊' union all
select '123123','啊啊' union all
select '123123','宝贝' union all
select '123456','尺寸' union all
select '123456','味道' union all
select '123456','低调' union all
select '654321','方法' union all
select '654321','额额' union all
select '456789','前期'
go
select distinct x.a,x.b from a as x where exists(select 1 from a as y
where x.a=y.a and x.b<>y.b)
/**
a b
--------------
123123 啊啊
123123 宝贝
123456 尺寸
123456 低调
123456 味道
654321 额额
654321 方法
--------------
**/
------解决思路----------------------
