SQ语句统计的问题(高分)(100分)

M

motorola168

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-15

• #1

表结构如下<br>表A<br>jobid &nbsp;int<br>jobby &nbsp;varchar<br>jobdate datetime<br>表B <br>jobid int<br>jobby varchar<br>local_amount foalt<br>表C<br>jobid int<br>job_amount float<br>job_type char<br>我现在要做的是1:<br>统计表A中jobid的收入和,和的计算办法是表B中loacl_amount（loacl_amount大于0时）和以及表C中job_amount的和(表Cjob_type=‘I’)并且表B和表C通过jobid和表A连接。统计的job_id必须在三个表中都存在。<br>2 统计支出 支出的计算办法是表B中loacl_amount（loacl_amount小于0时）和以及表C中job_amount的和(表Cjob_type=‘E’)并且表B和表C通过jobid和表A连接。统计的job_id必须在三个表中都存在。<br>用一条SQL怎么写出来？

 

A

Andyli

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-15

• #2

SELECT A.JOBID,<br>(SELECT SUM(local_amount) FROM B WHERE A.JOBID=B.JOBID AND B.loacl_amount&gt;0) <br>+ <br>(SELECT SUM(job_amount) FROM C WHERE C.JOBID=A.JOBID AND C.Cjob_type='I') AS income,<br>(SELECT SUM(local_amount) FROM B WHERE A.JOBID=B.JOBID AND B.loacl_amount&lt;0) <br>+ <br>(SELECT SUM(job_amount) FROM C WHERE C.JOBID=A.JOBID AND C.Cjob_type='E') &nbsp;AS PAY <br>FROM A GROUP BY JOBID

 

M

motorola168

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-15

• #3

Andyli 你这个语句跟我写的一样，但是统计结果不正确

 

A

Andyli

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-15

• #4

要加一个ISNULL的判断。<br>SELECT A.JOBID,<br>ISNULL( (SELECT SUM(local_amount) FROM B WHERE A.JOBID=B.JOBID AND B.loacl_amount&gt;0) ,0)<br>+ <br>ISNULL( (SELECT SUM(job_amount) FROM C WHERE C.JOBID=A.JOBID AND C.Cjob_type='I') ,0) AS income,<br>ISNULL( (SELECT SUM(local_amount) FROM B WHERE A.JOBID=B.JOBID AND B.loacl_amount&lt;0) ,0) <br>+ <br>ISNULL( (SELECT SUM(job_amount) FROM C WHERE C.JOBID=A.JOBID AND C.Cjob_type='E'),0) &nbsp;AS PAY <br>FROM A GROUP BY JOBID

 

M

motorola168

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-15

• #5

我用的是interbase数据库。没有isnull这个函数。

 

M

motorola168

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-16

• #6

还是不行啊

 

斯

斯文败类

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-16

• #7

select a.jobid,<br>sum(case when b.local_amount&gt;0 then b.local_amount else 0 end) income,<br>sum(case when c.job_type='E' then c.job_amount else 0 end) pay<br>from a,b,c<br>where a.jobid=b.jobid<br>and a.jobid=c.jobid<br>group by a.jobid;<br><br>在oracle上测试OK

 

M

motorola168

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-12-16

• #8

多人接受答案了。