Left Join问题

[pjwistc]

SELECT t.EmployeeID, isnull(sum(t1.value),0) as c1,
isnull(sum(t2.value),0) as c2 ,
isnull(sum(t3.value),0) as c3

FROM EMPLOYEE t left join Table1 t1 on t1.EmployeeID=t.EmployeeID
and t1.type=10 and t1.flag=1
left join Table1 t2 on t2.EmployeeID=t.EmployeeID
and t2.type=11 and t1.flag=1
left join Table1 t3 on t3.EmployeeID=t.EmployeeID
and t3.type=11 and t3.flag=1

Where t.cid=110
group by t.EmployeeID
order by t.EmployeeID

现在结果
employeeid c1 c2 c3
1 10 100 200
2 12 200 300
3 10 100 400


而正确结果是
employeeid c1 c2 c3
1 10 50 100
2 12 100 150
3 10 50 200


就是第1个字段SUM的字段正常 后面的都是翻了1倍

[忆轩辕]

确定逻辑上用lfet join?

用full join看看

[--小F--]

就这样还是难猜到

[让你望见影子的墙]

and t1.type=10 and t1.flag=1
and t2.type=11 and t1.flag=1
and t3.type=11 and t3.flag=1


放到where后面试试

[luoyoumou]

and t1.type=10 and t1.flag=1
left join Table1 t2 on t2.EmployeeID=t.EmployeeID
and t2.type=11 and t1.flag=1
left join Table1 t3 on t3.EmployeeID=t.EmployeeID
and t3.type=11 and t3.flag=1

[luoyoumou]

[Quote=引用 3 楼 luoyoumou 的回复:]
and t1.type=10 and t1.flag=1
left join Table1 t2 on t2.EmployeeID=t.EmployeeID
and t2.type=11  andt1.flag=1
left join Table1 t3 on t3.EmployeeID=t.EmployeeID
and t3.type=11  and t3.flag=1


[/Quote]

应该是：10 改为 11; t1 改为 t2 吧？

[ai_li7758521]

t1.type=10 and t1.flag=1
t2.type=11  and t1.flag=1
t3.type=11  and t3.flag=1
放到where后

[pjwistc]

放到WHERE 后 还是一样 就第1列正常

[pjwistc]

and t1.type=10 and t1.flag=1
left join Table1 t2 on t2.EmployeeID=t.EmployeeID
and t2.type=11 and t1.flag=1
left join Table1 t3 on t3.EmployeeID=t.EmployeeID
and t3.type=11 and t3.flag=1

这里是打出了刚刚
and t2.type=11 and t2.flag=1