我想如何显示关联四个表opp_dn相同字段值之间关联度大于2的结果集
假设有A、B、C、D四个表
A B CD
----- ----- ----- -----
opp_dn opp_dn opp_dn opp_dn
110 110 112 112
112 112 120 120
114 114
执行下面SQL语句：
SELECT tmpT.opp_dn,
MAX(CASE WHEN tmpT.Flag = 'B ' THEN 1 ELSE ' ' END) AS A,
MAX(CASE WHEN tmpT.Flag = 'B ' THEN 1 ELSE ' ' END) AS B,
MAX(CASE WHEN tmpT.Flag = 'C ' THEN 1 ELSE ' ' END) AS C,
MAX(CASE WHEN tmpT.Flag = 'C ' THEN 1 ELSE ' ' END) AS D
FROM (
SELECT A.*, 'A ' AS Flag FROM A
UNION ALL
SELECT B.*, 'B ' AS Flag FROM B
UNION ALL
SELECT C.*, 'C ' AS Flag FROM C
UNION ALL
SELECT D.*, 'D ' AS Flag FROM D
) tmpT GROUP BY tmpT.opp_dn
下面是结果集：
Opp A B C D
----- -------------------------------
110 1100
1121111
1130000
1141100
1200011
我想如何显示关联四个表opp_dn相同字段值之间关联度大于2的结果集，例如：
Opp A B C D
---- -------------------------------
1121111
110 11
11411
12011
[解决办法]
create table A(opp_dn int)
insert A select 110
union all select 110
union all select 112
union all select 114
union all select 113
create table B(opp_dn int)
insert B select 110
union all select 112
union all select 114
create table C(opp_dn int)
insert C select 112
union all select 120
create table D(opp_dn int)
insert D select 112
union all select 120
SELECT tmpT.opp_dn,
sum(CASE WHEN tmpT.Flag = 'A ' THEN 1 ELSE 0 END) AS A,
sum(CASE WHEN tmpT.Flag = 'B ' THEN 1 ELSE 0 END) AS B,
sum(CASE WHEN tmpT.Flag = 'C ' THEN 1 ELSE 0 END) AS C,
sum(CASE WHEN tmpT.Flag = 'D ' THEN 1 ELSE 0 END) AS D
FROM (
SELECT A.*, 'A ' AS Flag FROM A
UNION ALL
SELECT B.*, 'B ' AS Flag FROM B
UNION ALL
SELECT C.*, 'C ' AS Flag FROM C
UNION ALL
SELECT D.*, 'D ' AS Flag FROM D
) tmpT GROUP BY tmpT.opp_dn having count(*)> 1
--result
opp_dn A B C D
----------- ----------- ----------- ----------- -----------
110 2 1 0 0
112 1 1 1 1
114 1 1 0 0
120 0 0 1 1
(4 row(s) affected)
