编号 值
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
解决方案 »
- 急问: sql server中调用其它数据库服务器上数据时,一般都用哪些方法呢?
- 各位高人请帮我解答一个问题?
- 分享解决连续时间问题的方法
- 如何把SQL2000中的表导出到ACCESS中(*生成新的库和表*)?
- 如何把gdb转换成sql数据库???
- 怎么样把execl文件导入到MSSQL数据库表中??
- 把Excel中的数据导入到表中出现的错误
- a,b两字串相加,其中a或b为空值,c=a+b,则c 为空值,请问有何方法 使得这种情况下a+b不为空?
- 请教vfp大虾-----一个简单的问题??
- 请问我想把一个变量设为NULL,怎么办?把一个字段设为NULL,怎么办?
- sql 2000分组查询!!!
- 请教一条SQL 2005合并多行的查询语句
编号
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,希望大家不吝赐教,刚学了自身连接,故一试身手,嘿嘿