其中要求A、B、C、D的所有记录数据都出现在E中
解决方案 »
- 在线等!!!!!问个存储过程调用的问题
- 可以根据已知表创建一个新表?
- 求一条SQL语句,头都想痛了,求大家帮忙了
- 问:数据库用户和登录用户有什么区别和联系?(分开阐述,比如说他们是一对一还是一对多的关系)
- 存储过程错误“3265”,请大家帮忙解决?
- 表的主键为自动增量字段,有字段设为不能为空 但是程序里不为空的字段依旧能够插入,不出错
- sql 分组查询统计问题,期待您的解决啊~~
- 急急急:一个SQL不知道怎么写,SQL高手进!
- 如何将两个select得到的结果合成一张表输出
- 新增登陆用户和分配角色、指定权限的SQL怎么写(sql server 2000)
- 请问哪位知道在sqlserver2000里能识别外国得文字?高分相送!!
- 关于左外联接的问题,求各位大侠指点迷津小弟感激不尽。
UNION ALL
SELECT PK,NULL AS AF,BF,NULL AS CF,NULL AS DF FROM B
UNION ALL
SELECT PK,NULL AS AF,NULL AS BF,CF,NULL AS DF FROM C
UNION ALL
SELECT PK,NULL AS AF,NULL AS BF,NULL AS CF,DF FROM D
FROM A CROSS JOIN
B CROSS JOIN
C CROSS JOIN
D
3楼的方法存在一个问题,对于表A不存在的FK,而表B、C或D存在的FK就没有出现在结果集中了,是么
full outer join b on a.id=b.id
full outer join c on a.id=c.id
full outer join d on a.id=d.id
UNION ALL
SELECT 'B',PK,NULL AS AF,BF,NULL AS CF,NULL AS DF FROM B
UNION ALL
SELECT 'C',PK,NULL AS AF,NULL AS BF,CF,NULL AS DF FROM C
UNION ALL
SELECT 'D',PK,NULL AS AF,NULL AS BF,NULL AS CF,DF FROM D这样就能分的清了
表B为TABLE20050202_B
表C为TABLE20050202_C
表D为TABLE20050202_DSQL语句如下:select AllPK.PK, A.AF, B.BF, C.CF, D.DF
from (select distinct PK from
(select PK from TABLE20050202_A
UNION ALL
select PK from TABLE20050202_B
UNION ALL
select PK from TABLE20050202_C
UNION ALL
select PK from TABLE20050202_D) AllPK1) AllPK
left join TABLE20050202_A A on A.pk=AllPK.PK
left join TABLE20050202_B B on B.pk=AllPK.PK
left join TABLE20050202_C C on C.pk=AllPK.PK
left join TABLE20050202_D D on D.pk=AllPK.PK
楼主 看看是不是你想要的
select PK=(case
when a.pk is not null then a.pk
when b.pk is not null then b.pk
when c.pk is not null then c.pk
when d.pk is not null then d.pk
end) ,a.af,b.bf,c.cf,d.df
from afull outer join b on a.id=b.id
full outer join c on a.id=c.id
full outer join d on a.id=d.id
表B为TABLE20050202_B
表C为TABLE20050202_C
表D为TABLE20050202_DSQL语句如下:select AllPK.PK, A.AF, B.BF, C.CF, D.DF
from (select distinct PK from
(select PK from TABLE20050202_A
UNION ALL
select PK from TABLE20050202_B
UNION ALL
select PK from TABLE20050202_C
UNION ALL
select PK from TABLE20050202_D) AllPK1) AllPK
left join TABLE20050202_A A on A.pk=AllPK.PK
left join TABLE20050202_B B on B.pk=AllPK.PK
left join TABLE20050202_C C on C.pk=AllPK.PK
left join TABLE20050202_D D on D.pk=AllPK.PK
补充说明:
测试生成资料
A表中PK为1,2,3,4......50000 (50000以下的资料)
B表中PK为0,2,4,6......100000 (100000以内偶数的资料)
C表中PK为1,3,5,7......99999 (100000以内奇数的资料)
D表中PK为50001,50002,50003,50004......100000 (50000以上100000以内的资料)总共200000笔交叉资料,SQL语句共执行7秒钟,执行结果无误
机器配置环境(PIII667,256M,Windows2000 Server,SQLServer2000)
update e set bf = b.bf from a,b where b.pk = e.pkinsert into e(pk,cf) select pk,cf from c where pk not in (select pk from e)
update e set cf = c.cf from a,c where c.pk = e.pkinsert into e(pk,df) select pk,df from d where pk not in (select pk from e)
update e set df = d.df from a,d where d.pk = e.pkselect * from e
Horsezhao与hongbo163的解答都能够达到我的要求!性能方面Horsezhao多了一个查询
谢谢大家!
我写的那个还是存在问题的
horsezhao(horsezhao)的是正确的