编号 值
A 001
A 002
A 003B 001
B 002C 001D 005
D 006
D 007如果编号中值有两个或两个以上 我们就说编号相似
上面的有4组数据, A B有2个值相同,查询的数据结果为A B
A 001
A 002
A 003B 001
B 002C 001D 005
D 006
D 007如果编号中值有两个或两个以上 我们就说编号相似
上面的有4组数据, A B有2个值相同,查询的数据结果为A B
编号
from
tb
where
编号 in(select 编号 from tb group by 编号 having count(1)>1)
create table test(col1 varchar(10),col2 varchar(10))insert test select 'A','001'
union all select 'A','002'
union all select 'B','001'
union all select 'B','002'
union all select 'B','003'
union all select 'D','001'
union all select 'E','005'
union all select 'E','006'
union all select 'F','007';with t1 as
(
select col2 from test
group by col2
having(count(*) >=2)
)
select col1 from test t2 right join t1 on t2.col2=t1.col2
group by col1 having(count(*) >=2)drop table testcol1
----------
A
B
;with t as
(select 'A' as id, 001 as value union all
select 'A', 002 union all
select 'A', 003 union all
select 'B', 001 union all
select 'B', 002 union all
select 'C', 001 union all
select 'D', 005 union all
select 'D', 006 union all
select 'D', 007)
select id from t where exists(select 1 from t a where a.id<>t.id and value=t.value)
group by id
having count(id)>=2
;with t as
(select 'A' as id, 001 as value union all
select 'A', 002 union all
select 'A', 003 union all
select 'B', 001 union all
select 'B', 002 union all
select 'C', 001 union all
select 'D', 005 union all
select 'D', 006 union all
select 'D', 007),
t1 as(
select t.id as id,a.id as 'col' from t inner join t a on
t.id<>a.id and t.value=a.value
where 1=1
group by t.id,a.id
having count(a.id)>=2)
select id,(stuff((select ','+col from t1 c where t1.id=c.id for xml path('')),1,1,'' )) from t1
我觉得这么更合理
create table test(col1 varchar(10),col2 varchar(10))insert test select 'A','001'
union all select 'A','002'
union all select 'B','001'
union all select 'B','002'
union all select 'B','003'
union all select 'D','001'
union all select 'E','005'
union all select 'E','006'
union all select 'F','007'
select * from testwith
cr as
(select a.col1 as acol1 ,a.col2 as acol2,b.col1 as bcol1,b.col2 as bcol2
from test as a,test as b where a.col2=b.col2 and a.col1<>b.col1)
select acol1 from cr
group by acol1,bcol1 having(count(*)>=2)
/*
acol1
----------
B
A
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-04-06 15:03:53
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(1),[值] varchar(3))
insert [tb]
select 'A','001' union all
select 'A','002' union all
select 'A','003' union all
select 'B','001' union all
select 'B','002' union all
select 'C','001' union all
select 'D','005' union all
select 'D','006' union all
select 'D','007'
--------------开始查询--------------------------
select
编号
from
tb t
where
exists(select 1 from tb where 编号<>t.编号 and 值=t.值)
group by
编号
having count(1)>=2
----------------结果----------------------------
/* 编号
----
A
B(2 行受影响)*/
use tempdb;
/*
create table t1
(
编号 nvarchar(10) not null,
值 nvarchar(10) not null
);
insert into t1(编号,值)
values
('A','001'),
('A','002'),
('A','003'),
('B','001'),
('B','002'),
('C','001'),
('D','005'),
('D','006'),
('D','007');
*/
select t1.编号
from t1
join t1 as t2
on t1.编号 <> t2.编号 and t1.值 = t2.值
group by t1.编号
having COUNT(t1.值) > 2;
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(1),[值] varchar(3))
insert [tb]
select 'A','001' union all
select 'A','002' union all
select 'A','003' union all
select 'B','001' union all
select 'B','002' union all
select 'C','001' union all
select 'D','005' union all
select 'D','006' union all
select 'D','007'
----查询
select 编号 from
tb t
where exists(select 1 from tb where 编号<>t.编号 and 值=t.值)
group by编号
having count(1)>=2-------结果
/* 编号
---------
A
B
(2 行受影响)
*/
union all select 'A','002'
union all select 'B','001'
union all select 'B','002'
union all select 'B','003'
union all select 'D','001'
union all select 'E','005'
union all select 'E','006'
union all select 'F','007'
select col1 from test t
where exists(select 1 from test where col1<>t.col1 and col2=t.col2)
group by col1 having count(col1)>=2
select X.编号
from t1 as X,t1 as Y
where Y.值=X.值 and X.编号!=Y.编号
group by X.编号,Y.编号 having count(Y.编号)>1
偶才刚开始学习sql,希望大家不吝赐教,刚学了自身连接,故一试身手,嘿嘿