表A
--------------------------------------
ID UID DATE T1 B1 T3
1 A 2010-05-01 80 100 1
2 B 2010-06-03 90 200 1
3 C 2010-06-04 50 80 1
---------------------------------------
表B
----------------------------
ID UID Q1 Q3
1 A 30 1
2 A 40 2
3 B 200 1
4 C 10 1
5 C 35 2
----------------------------
现在我想要把表A以UID为标识的减去表B的以UID为标识的等0的不联接。
如:
select a.id,a.uid,a.date,a.t1,a.b1,B.UID,B.B1 from [表A] FULL OUTER JOIN
[表B] ON A.UID=B.UID AND A.T3=B.T3
,但现在我如何判断表A以UID为标识的减去表B的以UID为标识的等0。
原先我想
select a.id,a.uid,a.date,a.t1,a.b1,B.UID,B.Q1 from [表A] FULL OUTER JOIN
[表B] ON A.UID=B.UID AND A.T3=B.Q3 left outer join (select id,sum(b1)as b1 from [表A] GROUP BY id)c
left outer join (select uid,sum(Q1) as Q1 FROM [表B] group by uid)d on c.id=d.uid where (c.b1-d.q1>0)
现在是不能通过的。请各位大侠帮忙。谢谢!!
--------------------------------------
ID UID DATE T1 B1 T3
1 A 2010-05-01 80 100 1
2 B 2010-06-03 90 200 1
3 C 2010-06-04 50 80 1
---------------------------------------
表B
----------------------------
ID UID Q1 Q3
1 A 30 1
2 A 40 2
3 B 200 1
4 C 10 1
5 C 35 2
----------------------------
现在我想要把表A以UID为标识的减去表B的以UID为标识的等0的不联接。
如:
select a.id,a.uid,a.date,a.t1,a.b1,B.UID,B.B1 from [表A] FULL OUTER JOIN
[表B] ON A.UID=B.UID AND A.T3=B.T3
,但现在我如何判断表A以UID为标识的减去表B的以UID为标识的等0。
原先我想
select a.id,a.uid,a.date,a.t1,a.b1,B.UID,B.Q1 from [表A] FULL OUTER JOIN
[表B] ON A.UID=B.UID AND A.T3=B.Q3 left outer join (select id,sum(b1)as b1 from [表A] GROUP BY id)c
left outer join (select uid,sum(Q1) as Q1 FROM [表B] group by uid)d on c.id=d.uid where (c.b1-d.q1>0)
现在是不能通过的。请各位大侠帮忙。谢谢!!
from [表A]
FULL OUTER JOIN [表B] ON A.UID=B.UID AND A.T3=B.Q3
left outer join (select id,sum(b1)as b1 from [表A] GROUP BY id)c
left outer join (select uid,sum(Q1) as Q1 FROM [表B] group by uid)d on c.id=d.uid
where (c.b1-d.q1>0)格式一下,显然c没有连接条件
哦,除了uid还有限制tryselect a.id,a.uid,a.date,a.t1,a.b1,B.UID,B.Q1
from [表A] as a
FULL OUTER JOIN [表B] as b ON A.UID=B.UID AND A.T3=B.Q3
where a.b1-b.q1>0
from [表A] as a
FULL OUTER JOIN [表B] as b ON A.UID=B.UID AND A.T3=B.Q3
where a.b1-b.q1>0这样不符合我的要求,因为where a.b1-b.q1>0中是select uid,sum(b1)as b1 from [表A] 减 select uid,SUM(q1) as q1 from [表B] GROUP BY UID 以表A的UID和表B的UID对应 (a.uid=b.uid)大于0。
from [表A]
FULL OUTER JOIN [表B] ON A.UID=B.UID AND A.T3=B.Q3
where a.uid in(select uid
from (select uid,sum(b1)as b1 from [表A] GROUP BY uid)c
left outer join (select uid,sum(Q1) as Q1 FROM [表B] group by uid)d on c.uid=d.uid
where (c.b1-d.q1>0)
)
from [表A] as a
FULL OUTER JOIN [表B] as b ON A.UID=B.UID AND A.T3=B.Q3
where a.uid in(select c.uid
from (select uid,sum(b1)as b1 from [表A] GROUP BY uid)c
left outer join (select uid,sum(Q1) as Q1 FROM [表B] group by uid)d on c.uid=d.uid
where (c.b1-d.q1>0)
)