假设有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
----- ----- ----- -----
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
解决方案 »
- VF中如何设置索引!!大师们帮忙啊
- SQL2005中怎样判断一组数字是否为连续数?
- 急!SQL Server的存储过程中,如何返回以 EXEC @SQL方式取到的数据集?
- 请教用在VC++中用ADO开发数据库时,编译问题,谢谢
- try catch transaction rollback
- 求一sql 查询语句,很有挑战。
- 在英国的MS-SQL数据库生成的日期格式原来的:'27/05/2013 10:05:51'要改为什么内容,才能在各种日期不同的数据库中正确插入呢?
- vfp 精选问题两篇!解决立即给分,200分不够再加!希望能得到你的指点!最好能详细点!有详细代码并能解决200送!
- 大家来看看这个(冒着封ID的危险第一次散分,来者有份)
- 寻求简单的VC编写的ADO源代码,最好有例子。
- 加一列升序列号码
- 数据库文件名未知,怎么判断?
MAX(CASE WHEN tmpT.Flag = 'A' 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 = 'D' 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
insert A select 110
union all select 112
union all select 114create table B(opp_dn int)
insert B select 110
union all select 112
union all select 114create table C(opp_dn int)
insert C select 112
union all select 120create table D(opp_dn int)
insert D select 112
union all select 120 SELECT tmpT.opp_dn,
MAX(CASE WHEN tmpT.Flag = 'A' 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 = 'D' 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--result
opp_dn A B C D
----------- ----------- ----------- ----------- -----------
110 1 1 0 0
112 1 1 1 1
114 1 1 0 0
120 0 0 1 1(4 row(s) affected)
create table A(opp_dn int)
insert A select 110
union all select 112
union all select 114
union all select 113
create table A(opp_dn int)
insert A select 110
union all select 112
union all select 114
union all select 113create table B(opp_dn int)
insert B select 110
union all select 112
union all select 114create table C(opp_dn int)
insert C select 112
union all select 120create table D(opp_dn int)
insert D select 112
union all select 120 SELECT tmpT.opp_dn,
MAX(CASE WHEN tmpT.Flag = 'A' 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 = 'D' 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 having count(*)>1--result
opp_dn A B C D
----------- ----------- ----------- ----------- -----------
110 1 1 0 0
112 1 1 1 1
114 1 1 0 0
120 0 0 1 1(4 row(s) affected)
insert A select 110
union all select 110
union all select 112
union all select 114
union all select 113应该如何实现下面的结果呢?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
insert A select 110
union all select 110
union all select 112
union all select 114
union all select 113create table B(opp_dn int)
insert B select 110
union all select 112
union all select 114create table C(opp_dn int)
insert C select 112
union all select 120create 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)