各位专家:
若有以下两列数据(Con1和Con2为关联的一对数据)
Con1 Con2
A B
A C
A D
A E
B C
B D
B E
C D
C F
...
如何找出有两两关系的一组数据哦?如A-B-C,A-B-C-D,B-C-D的两两有关联的数据。求解,谢谢!
若有以下两列数据(Con1和Con2为关联的一对数据)
Con1 Con2
A B
A C
A D
A E
B C
B D
B E
C D
C F
...
如何找出有两两关系的一组数据哦?如A-B-C,A-B-C-D,B-C-D的两两有关联的数据。求解,谢谢!
解决方案 »
- 续select top 1 studentid from
- 求一个存储过程...在线等..~~
- 求一个Acc中可用的联合查询SQL语句
- 最近在学数据库原理 有几个关于键的概念不太清楚 请教大家
- 简单的SQL查问题,懂的请问看看吧
- 怎么优化??????
- 吉林大学面向对象视频教学的种子,有下的,就快点去下。
- update问题求教
- ??????????????安装proxy client后,为什么SQL SERVER 不能连接 ??????
- 登录本地服务器失败,请验证实例名称是否正确并且SQL Server已配置为允许远程连接
- 连接sql server2008慢的问题
- MFC ODBC数据库编程时修改了数据库里面列属性出现的问题
AS(
SELECT con1 AS bg,con2 AS ed FROM dbo.TB
UNION ALL
SELECT con2,con1 FROM TB),NextW AS(
SELECT bg,ed,CASt('-'+bg+'-'+ed+'-' AS varchar(MAX)) AS ww
FROM TT
UNION ALL
SELECT A.bg,B.ed,CAST(A.ww+B.ed+'-' AS VARCHAR(MAX)) FROM NextW AS A
INNER JOIN TT B ON CASE WHEN A.ww LIKE '%-'+B.ed +'-%' THEN 1 ELSE 0 END =0
AND A.ed = b.bg)SELECT REPLACE(ww,'-','') AS ww FROM NextW WHERE EXISTS(SELECT 1 FROM TB WHERE bg =con1)/*
AB
AC
AD
AE
BC
BD
BE
CD
CF
BA
CA
CB
CBD
CBE
CBA
CBAD
CBAE
CBEA
CBEAD
CBDA
CBDAE
CAB
CAD
CAE
CAEB
CAEBD
CADB
CADBE
CABD
CABE
BAC
BAD
BAE
BADC
BADCF
BACD
BACF
CDA
CDB
CDBE
CDBA
CDBAE
CDBEA
CDAB
CDAE
CDAEB
CDABE
BEA
BEAC
BEAD
BEADC
BEADCF
BEACD
BEACF
BDA
BDC
BDCF
BDCA
BDCAE
BDAC
BDAE
BDACF
BCD
BCF
BCA
BCAD
BCAE
BCDA
BCDAE
AEB
AEBC
AEBD
AEBDC
AEBDCF
AEBCD
AEBCF
ADB
ADC
ADCF
ADCB
ADCBE
ADBC
ADBE
ADBCF
ACD
ACF
ACB
ACBD
ACBE
ACDB
ACDBE
ABC
ABD
ABE
ABDC
ABDCF
ABCD
ABCF*/
AS(
SELECT con1 AS bg,con2 AS ed FROM dbo.TB
),NextW AS(
SELECT bg,ed,CASt('-'+bg+'-'+ed+'-' AS varchar(MAX)) AS ww
FROM TT
UNION ALL
SELECT A.bg,B.ed,CAST(A.ww+B.ed+'-' AS VARCHAR(MAX)) FROM NextW AS A
INNER JOIN TT B ON CASE WHEN A.ww LIKE '%-'+B.ed +'-%' THEN 1 ELSE 0 END =0
AND A.ed = b.bg)SELECT REPLACE(ww,'-','') AS ww FROM NextW WHERE LEN(REPLACE(ww,'-','')) >=3
ORDER BY REPLACE(ww,'-','')/*
ABC
ABCD
ABCF
ABD
ABE
ACD
ACF
BCD
BCF*/
;WITH TT
AS(
SELECT con1 AS bg,con2 AS ed FROM dbo.TB
),
NextW AS(
SELECT bg,ed,CASt('-'+bg+'-'+ed+'-' AS varchar(MAX)) AS ww
FROM TT
UNION ALL
SELECT A.bg,B.ed,CAST(A.ww+B.ed+'-' AS VARCHAR(MAX)) FROM NextW AS A
INNER JOIN TT B ON CASE WHEN A.ww LIKE '%-'+B.ed +'-%' THEN 1 ELSE 0 END =0
AND A.ed = b.bg)
SELECT REPLACE(ww,'-','') AS ww FROM NextW WHERE LEN(REPLACE(ww,'-','')) >=3
ORDER BY REPLACE(ww,'-','')
/*
ABC
ABCD
ABCF
ABD
ABE
ACD
ACF
BCD
BCF*/