多表查询语句，想合成一张表但没成功，郁闷！
有两个表A，B，例：
A
id b c d f
1 22 13 16 12
2 31 14 12 14
B
id g h
1 3 3
2 2 5
3 8 9
想得到C表
id b c d f g h
1 22 13 16 12 3 3
2 31 14 12 14 2 5
3 0 0 0 0 8 9
这个语句应该怎么来写呢？实在想不出来了。。。。
[解决办法]
create table A(id int,b int,c int,d int,f int)insert into a values(1 ,22 ,13 ,16 ,12) insert into a values(2 ,31 ,14 ,12 ,14) create table B(id int,g int,h int)insert into b values(1 ,3, 3) insert into b values(2 ,2, 5) insert into b values(3 ,8, 9)goselect isnull(a.id,b.id) id, isnull(a.b,0) b, isnull(a.c,0) c, isnull(a.d,0) d, isnull(a.f,0) f, isnull(b.g,0) g, isnull(b.h,0) hfrom a full join b on a.id = b.iddrop table a , b/*id b c d f g h ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 22 13 16 12 3 32 31 14 12 14 2 53 0 0 0 0 8 9（所影响的行数为 3 行）*/
[解决办法]
if object_id('[TA]') is not null drop table [TA]create table [TA]([id] int,[b] int,[c] int,[d] int,[f] int)insert [TA]select 1,22,13,16,12 union allselect 2,31,14,12,14if object_id('[TB]') is not null drop table [TB]create table [TB]([id] int,[g] int,[h] int)insert [TB]select 1,3,3 union allselect 2,2,5 union allselect 3,8,9select TB.id, b=isnull(b,0), c=isnull(c,0), d=isnull(d,0), f=isnull(f,0), g=isnull(g,0), h=isnull(h,0) from [TA] right join TB on TA.id=TB.id/*id b c d f g h----------- ----------- ----------- ----------- ----------- ----------- -----------1 22 13 16 12 3 32 31 14 12 14 2 53 0 0 0 0 8 9(3 行受影响)*/drop table TA,TB
[解决办法]
插入临时表再按上面的查不就一样了,插入临时表可能效率要好点
SELECT * ,(co+co3) AS co4 INTO TAFROM (SELECT userid,uname,sendarc_num,(SELECT VALUE FROM AAA_sysconfig WHERE aid=729 AND groupid=5)AS a ,sendarc_num*(SELECT VALUE FROM AAA_sysconfig WHERE aid=729 AND groupid=5) AS co ,arcpub_num,(SELECT VALUE FROM AAA_sysconfig WHERE aid=7230 AND groupid=5) AS co2,arcpub_num*(SELECT VALUE FROM AAA_sysconfig WHERE aid=7230 AND groupid=5) AS co3FROM AAA_member)tb1SELECT * INTO TB FROM (SELECT SUM(sign.point) AS POINT,COUNT(mb.mid) AS total, mb.*,user.userid,user.unameFROM `AAA_sign_mb` mbLEFT JOIN `AAA_sign_arc` SIGN ON sign.aid=mb.aidLEFT JOIN `AAA_member` USER ON mb.mid=user.midGROUP BY mb.mid )tb2
[解决办法]
select b.id, b=isnull(b,0), c=isnull(c,0), d=isnull(d,0), f=isnull(f,0), g=isnull(g,0), h=isnull(h,0) from (SELECT userid,uname,sendarc_num,(SELECT VALUE FROM AAA_sysconfig WHERE aid=729 AND groupid=5)AS a ,sendarc_num*(SELECT VALUE FROM AAA_sysconfig WHERE aid=729 AND groupid=5) AS co ,arcpub_num,(SELECT VALUE FROM AAA_sysconfig WHERE aid=7230 AND groupid=5) AS co2,arcpub_num*(SELECT VALUE FROM AAA_sysconfig WHERE aid=7230 AND groupid=5) AS co3FROM AAA_member)a right join (SELECT SUM(sign.point) AS POINT,COUNT(mb.mid) AS total, mb.*,user.userid,user.unameFROM `AAA_sign_mb` mbLEFT JOIN `AAA_sign_arc` SIGN ON sign.aid=mb.aidLEFT JOIN `AAA_member` USER ON mb.mid=user.midGROUP BY mb.mid )b on a.id=b.id
[解决办法]
if object_id('[TA]') is not null drop table [TA]create table [TA]([id] int,[b] int,[c] int,[d] int,[f] int)insert [TA]select 1,22,13,16,12 union allselect 2,31,14,12,14if object_id('[TB]') is not null drop table [TB]create table [TB]([id] int,[g] int,[h] int)insert [TB]select 1,3,3 union allselect 2,2,5 union allselect 3,8,9select isnull(A.id,B.id) id, isnull(A.[b],0) [b], isnull(A.[c],0) [c], isnull(A.[d],0) [d], isnull(A.[f],0) [f], b.[g],b.[h]from [TA] Afull join [TB] Bon A.[id]=B.[id]id b c d f g h----------- ----------- ----------- ----------- ----------- ----------- -----------1 22 13 16 12 3 32 31 14 12 14 2 53 0 0 0 0 8 9(3 行受影响)
[解决办法]
--把第一个语句修改下，然后安装1，2楼的join--没有关联的子查询 直接换成变量算了declare @VALUE dec(38,2),@VALUE2 dec(38,2)SELECT @VALUE=[VALUE] FROM AAA_sysconfig WHERE aid=729 AND groupid=5SELECT @VALUE2=[VALUE] FROM AAA_sysconfig WHERE aid=7230 AND groupid=5SELECT userid, uname, sendarc_num, @VALUE a , sendarc_num*@VALUE AS co, arcpub_num, @VALUE2 AS co2, arcpub_num*@VALUE2 AS co3, sendarc_num*@VALUE+arcpub_num*@VALUE2 co4FROM AAA_member
[解决办法]