怎样才能实现，GROUP BY OR ？

[zitjubiz]

我有一个人员表emp（empid,name）
一个项目表pro(pjid,name,season)
一个费用表fee(pjid,empid,fee1,fee2)

我要把每个人员的属于同一季度的项目的费用的合计找出来。
select emp.name,sum(fee1),sum(fee2) from
from emp left join fee on emp.empid=fee.empid
join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name

但是我想把没有费用的人员也显示出来，显示费用为0
这个SQL我就不会写了。请指教....

[XLYT]

select emp.name,sum(fee1),sum(fee2) from
from emp left join fee on emp.empid=fee.empid
join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name
union all
select name,0,0 from emp
where empid not exists(select empid from fee)

[leimin]

try:
select emp.name,sum(fee1),sum(fee2)
from emp left join fee on emp.empid=fee.empid
inner join pro on fee.pjid=pro.pjid and pj.season=1
group by all fee.empid ,emp.[name]

[Rewiah]

select emp.name,isnull(fee1,0) as fee1,isnull(fee2,0) as fee2
from emp left join (select empid,sum(fee1) as fee1,sum(fee2) as fee2 from fee inner join pro on fee.pjid=pro.pjid and pj.season=1
) as fee1
on emp.empid=fee1.empid

[yoki]

select emp.name,isnull(sum(fee1),0),isnull(sum(fee2),0)
from emp left join fee on emp.empid=fee.empid
left join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name

[pengdali]

select emp.name,isnull(sum(fee1),0),isnull(sum(fee2),0) from emp left join fee on emp.empid=fee.empid left join pro on fee.pjid=pro.pjid and pj.season=1 group by fee.empid ,emp.name

[sksk]

select emp.name,sum(isnull(fee1),0),sum(isnull(fee2)) from emp left join fee on emp.empid=fee.empid inner join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name

[sksk]

select emp.name,sum(isnull(fee1),0),sum(isnull(fee2),0) from emp left join fee on emp.empid=fee.empid inner join pro on fee.pjid=pro.pjid and pro.season=1
group by fee.empid

[happydreamer]

select emp.name,isnull(sum(fee1),0),isnull(sum(fee2),0)
from emp left join fee on emp.empid=fee.empid
left join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name

[zitjubiz]

inner join pro 会把fee没有存在的empid 去掉
left join pro 会把pro不是第一季度的也加上去。
还是XLYT(雨田) Rewiah(乘长风) 的比较好,稍为改一下：
______________________________________________
select emp.name,sum(fee1),sum(fee2) from
from emp left join fee on emp.empid=fee.empid
join pro on fee.pjid=pro.pjid and pj.season=1
group by fee.empid ,emp.name
union all
select name,0,0 from emp
where empid not in(select empid from fee)
_________________________________________________
select emp.name,isnull(fee1,0) as fee1,isnull(fee2,0) as fee2
from emp left join (select empid,sum(fee1) as fee1,sum(fee2) as fee2 from fee inner join pro on fee.pjid=pro.pjid and pj.season=1 group by empid
) as fee1
on emp.empid=fee1.empid

[bluemeteor]

select emp.name,isnull(sum(fee1),0),isnull(sum(fee2),0) from emp left join fee on emp.empid=fee.empid left join pro on fee.pjid=pro.pjid and pj.season=1 group by fee.empid ,emp.name

[ComputerSoul]

楼上 bluemeteor 的解法还是会漏部分没有“一季度项目的费用”的员工的，
我以前碰到过类似情况，发现还是需要多一个连接才能达到目的。不过当时未能想第一种解法，所以就未能比较两者的效率来；现在又没有相应的数据来测试，因此请楼主能够测试一下，并告知我们等人。

再啰嗦几句。对比两个语句，发现除了两次查询员工表和一个连接是相同之外，两者的不同点在于第一个用了 UNION 加 WHERE ，第二个是用了再次连接。我以为 UNION 语句的速度会较连接的方式慢，所以，第二个解法的执行效率会较快一些。当然，这是想当然的推测，没有经过测试，望高手们能多多指正。

[lizongqi]

up

[jinzhouk]

我也碰到类似的问题，总是用第二种方法来解决的。
但看到这个贴子后，觉得如果只有一种分组，用第一种方法很简便。

[ComputerSoul]

同意以上两种解法，不知楼主或其他好手可以这样做呢：比较两种不同解法在数据量大的情况下有何区别，当然是执行效率方面啦。
或许从语法的分析也可看出哪个语句的执行效率高，不过鄙人自问根底不够扎实，还是让高手们来分析吧！

[zyf23]

ting

[mymmsc]

select emp.name,isnull(sum(fee1),0),isnull(sum(fee2),0) from emp left join fee on emp.empid=fee.empid left join pro on fee.pjid=pro.pjid and pj.season=1 group by fee.empid ,emp.name

[huhongming]

SELECT emp.[name],ISNULL(fee1,0) AS TotalFee1,ISNULL(fee2,0) AS TotalFee2
FROM emp LEFT JOIN
(SELECT fee.empid,sum(fee1) as fee1,sum(fee2) as fee2
FROM fee join pro on fee.pjid=pro.pjid and pro.season=1
GROUP BY fee.empid) aa
ON emp.empid=aa.empid

union的解法相比于上述解法，主要消耗在于多做了
select name,0,0 from emp
where empid not in(select empid from fee)
相当于又把emp和fee join了一次，执行计划表明多做了一次Hash Match/Left Ansi Semi Join.
用上述解法还有一个额外的好处就是结果是按照Empid排序的

[8LY8Apollo]

up