日期:2014-05-18  浏览次数:20336 次

我想如何显示关联四个表opp_dn相同字段值之间关联度大于2的结果集
假设有A、B、C、D四个表

    A                   B               C D
-----           -----           -----           -----  
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   1 1 0 0
112 1 1 1 1
113 0 0 0 0
114 1 1 0 0
120 0 0 1 1

我想如何显示关联四个表opp_dn相同字段值之间关联度大于2的结果集,例如:

Opp           A               B               C               D      
----   -------------------------------
112 1 1 1 1
110   1 1
114 1 1
120 1 1

------解决方案--------------------
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 '