新手求助，几张关联的表，如何将重复金额合并，不通金额相加

qq_29171973 2018-07-20 03:46:42

我这里有四张张表分别是项目表，客户工作表，兼职的支出表和公司的支出表，要通过客户表减去公司和兼职支出得出利润表，因为同一个项目有时候兼职公司都在做就出现重复项
下面是几张表的字段

这是通过PROJ_ID将几个表格的连接

SELECT DISTINCT
PROJECTS.PROJ_NUMB,
PROJECTS.PROJ_NAME,
CJOBS.CJOB_TOTAL,
EJOBS.EJOB_TOTAL,
RJOBS.RJOB_TOTAL
FROM PROJECTS,CJOBS,EJOBS,RJOBS

WHERE PROJECTS.PROJ_ID=RJOBS.PROJ_ID AND
PROJECTS.PROJ_ID=CJOBS.PROJ_ID AND
PROJECTS.PROJ_ID=EJOBS.PROJ_ID

得出结果中重复的数据很多，而且项目显示也不完整

这个才是要的结果

请问我要这么写，万分感谢

...全文

578 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

qq_29171973 2018-07-24

打赏
举报

引用 21 楼 BlueStorm 的回复:

你用的是什么数据库？ sqlserver里面是有isnull函数的。

HAHA可以了谢谢兄弟了

qq_29171973 2018-07-24

打赏
举报

引用 21 楼 BlueStorm 的回复:

你用的是什么数据库？ sqlserver里面是有isnull函数的。

用的FastReport 报表空间支持SQL

BlueStorm 2018-07-23

打赏
举报

第二行的RJOB_TOTAL写成了RJOB_TOTALL，导致报错，更改为：



SELECT PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL AS TOTAL, 

       (CJOB_TOTAL-EJOB_TOTAL-RJOB_TOTAL) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

         GROUP BY P.PROJ_NUMB, P.PROJ_NAME) AS T1;

qq_29171973 2018-07-23

打赏
举报

引用 6 楼 BlueStorm 的回复:



SELECT PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL, 

       (CJOB_TOTAL-EJOB_TOTAL-RJOB_TOTALL) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

         GROUP BY P.PROJ_NUMB, P.PROJ_NAME) AS T1;

BlueStorm 2018-07-23

打赏
举报

你用的是什么数据库？ sqlserver里面是有isnull函数的。

qq_29171973 2018-07-23

打赏
举报

引用 19 楼 BlueStorm 的回复:



这个才对：

WITH

  R AS (SELECT PROJ_ID, SUM(RJOB_TOTAL) AS RJOB_TOTAL FROM RJOBS GROUP BY PROJ_ID),

  E AS (SELECT PROJ_ID, SUM(EJOB_TOTAL) AS EJOB_TOTAL FROM EJOBS GROUP BY PROJ_ID),

  C AS (SELECT PROJ_ID, SUM(CJOB_TOTAL) AS CJOB_TOTAL FROM CJOBS GROUP BY PROJ_ID)

SELEC PROJ_NUMB, PROJ_NAME,

      ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

      ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

     (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

  FROM PROJECTS AS P

  LEFT JOIN R ON R.PROJ_ID=P.PROJ_ID

  LEFT JOIN E ON E.PROJ_ID=P.PROJ_ID

  LEFT JOIN C ON C.PROJ_ID=P.PROJ_ID

  ORDER BY PROJ_NUMB

BlueStorm 2018-07-23

打赏
举报



这个才对：

WITH

  R AS (SELECT PROJ_ID, SUM(RJOB_TOTAL) AS RJOB_TOTAL FROM RJOBS GROUP BY PROJ_ID),

  E AS (SELECT PROJ_ID, SUM(EJOB_TOTAL) AS EJOB_TOTAL FROM EJOBS GROUP BY PROJ_ID),

  C AS (SELECT PROJ_ID, SUM(CJOB_TOTAL) AS CJOB_TOTAL FROM CJOBS GROUP BY PROJ_ID)

SELEC PROJ_NUMB, PROJ_NAME,

      ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

      ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

     (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

  FROM PROJECTS AS P

  LEFT JOIN R ON R.PROJ_ID=P.PROJ_ID

  LEFT JOIN E ON E.PROJ_ID=P.PROJ_ID

  LEFT JOIN C ON C.PROJ_ID=P.PROJ_ID

  ORDER BY PROJ_NUMB

BlueStorm 2018-07-23

打赏
举报



更正一下

WITH

  R AS (SELECT PROJ_ID, SUM(RJOB_TOTAL) AS RJOB_TOTAL FROM RJOBS GROUP BY PROJ_ID),

  E AS (SELECT PROJ_ID, SUM(EJOB_TOTAL) AS EJOB_TOTAL FROM EJOBS GROUP BY PROJ_ID),

  C AS (SELECT PROJ_ID, SUM(CJOB_TOTAL) AS CJOB_TOTAL FROM CJOBS GROUP BY PROJ_ID)

SELEC PROJ_NUMB, PROJ_NAME,

      ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

      ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

     (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

  FROM PROJECTS AS P

  LEFT JOIN R ON R.PROJ_ID=P.PROJ_ID

  LEFT JOIN E ON R.PROJ_ID=P.PROJ_ID

  LEFT JOIN C ON R.PROJ_ID=P.PROJ_ID

  ORDER BY PROJ_NUMB

BlueStorm 2018-07-23

打赏
举报



这样写会好看一点（group by语句没有必要了吧？）：

WITH

  R AS (SELECT PROJ_ID, SUM(RJOB_TOTAL) AS RJOB_TOTAL FROM RJOBS GROUP BY PROJ_ID),

  E AS (SELECT PROJ_ID, SUM(EJOB_TOTAL) AS EJOB_TOTAL FROM EJOBS GROUP BY PROJ_ID),

  C AS (SELECT PROJ_ID, SUM(CJOB_TOTAL) AS CJOB_TOTAL FROM CJOBS GROUP BY PROJ_ID)

SELEC TPROJ_NUMB, PROJ_NAME,

      ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

      ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

     (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

  FROM PROJECTS AS P

  LEFT JOIN R ON R.PROJ_ID=P.PROJ_ID

  LEFT JOIN E ON E.PROJ_ID=P.PROJ_ID

  LEFT JOIN C ON C.PROJ_ID=P.PROJ_ID

  ORDER BY TPROJ_NUMB

qq_29171973 2018-07-23

打赏
举报

引用 13 楼 BlueStorm 的回复:



SELECT PROJ_NUMB, PROJ_NAME,

       ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

	   ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

       (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

我在想应该可以在这里面将NULL值替换

 LEFT OUTER JOIN

  (

   SELECT RJOBS.PROJ_ID,SUM(RJOBS.RJOB_TOTAL)AS RJOB_TOTAL

   FROM RJOBS

   GROUP BY RJOBS.PROJ_ID

  )AS T2

   ON T1.PROJ_ID=T2.PROJ_ID

qq_29171973 2018-07-23

打赏
举报

引用 14 楼 BlueStorm 的回复:



你上面的语句，应该就等效于下面的语句（group by语句根据你的写法来写）：

SELECT PROJ_NUMB, PROJ_NAME,

       ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

	   ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

      (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

		 GROUP BY PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL, EJOB_TOTAL) AS T1;

第一个你那个写法我就是那么写的，有错真是崩溃呀，太感谢你了

BlueStorm 2018-07-23

打赏
举报



你上面的语句，应该就等效于下面的语句（group by语句根据你的写法来写）：

SELECT PROJ_NUMB, PROJ_NAME,

       ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

	   ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

      (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

		 GROUP BY PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL, EJOB_TOTAL) AS T1;

BlueStorm 2018-07-23

打赏
举报



SELECT PROJ_NUMB, PROJ_NAME,

       ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

	   ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

       (ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

BlueStorm 2018-07-23

打赏
举报

SELECT 

    PROJ_NUMB, PROJ_NAME,CJOB_TOTAL,RJOB_TOTAL,EJOB_TOTAL

改为下面的语句就可以把空值转为0：

SELECT PROJ_NUMB, PROJ_NAME,

       ISNULL(CJOB_TOTAL, 0.0) AS CJOB_TOTAL,

	   ISNULL(RJOB_TOTAL, 0.0) AS RJOB_TOTAL,

       （ISNULL(CJOB_TOTAL, 0.0)-ISNULL(EJOB_TOTAL, 0.0)-ISNULL(RJOB_TOTAL, 0.0)) AS LIRUN

qq_29171973 2018-07-23

打赏
举报

引用 10 楼 BlueStorm 的回复:

那你就先核对一下项目180932的CJOB_TOTAL究竟是多少：
select sum(CJOB_TOTAL) as CJOB_TOTAL from CJOBS where PROJ_ID='180932'
这是个很简单的查询语句了，已经没有关联到任何其他的表，如果得出的结果还是不对，那证明是你的数据本身有问题。

数据没错误，我现在写了个你看下，和都求出来了，现在就是就利润了，但是产生的空值我始终不能替换为0

SELECT 

    PROJ_NUMB, PROJ_NAME,CJOB_TOTAL,RJOB_TOTAL,EJOB_TOTAL

  FROM PROJECTS AS T1

  LEFT OUTER JOIN

  (

   SELECT RJOBS.PROJ_ID,SUM(RJOBS.RJOB_TOTAL)AS RJOB_TOTAL

   FROM RJOBS

   GROUP BY RJOBS.PROJ_ID

  )AS T2

   ON T1.PROJ_ID=T2.PROJ_ID

   LEFT OUTER JOIN

  (

   SELECT EJOBS.PROJ_ID,SUM(EJOBS.EJOB_TOTAL)AS EJOB_TOTAL

   FROM EJOBS

   GROUP BY EJOBS.PROJ_ID

  )AS T3

   ON T1.PROJ_ID=T3.PROJ_ID

    LEFT OUTER JOIN

  (

   SELECT CJOBS.PROJ_ID,SUM(CJOBS.CJOB_TOTAL)AS CJOB_TOTAL

   FROM CJOBS

   GROUP BY CJOBS.PROJ_ID

  )AS T4

   ON T1.PROJ_ID=T4.PROJ_ID

GROUP BY PROJ_NUMB, PROJ_NAME,CJOB_TOTAL,RJOB_TOTAL,EJOB_TOTAL

BlueStorm 2018-07-23

打赏
举报

那你就先核对一下项目180932的CJOB_TOTAL究竟是多少：
select sum(CJOB_TOTAL) as CJOB_TOTAL from CJOBS where PROJ_ID='180932'
这是个很简单的查询语句了，已经没有关联到任何其他的表，如果得出的结果还是不对，那证明是你的数据本身有问题。

qq_29171973 2018-07-23

打赏
举报

引用 8 楼 BlueStorm 的回复:

第二行的RJOB_TOTAL写成了RJOB_TOTALL，导致报错，更改为：



SELECT PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL AS TOTAL, 

       (CJOB_TOTAL-EJOB_TOTAL-RJOB_TOTAL) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

         GROUP BY P.PROJ_NUMB, P.PROJ_NAME) AS T1;

算出来结果不对，对比看下前面的180932这个项目，数据差太多

BlueStorm 2018-07-20

打赏
举报



SELECT PROJ_NUMB, PROJ_NAME, CJOB_TOTAL, RJOB_TOTAL, 

       (CJOB_TOTAL-EJOB_TOTAL-RJOB_TOTALL) AS LIRUN 

 FROM (SELECT P.PROJ_NUMB, P.PROJ_NAME, 

              SUM(C.CJOB_TOTAL) AS CJOB_TOTAL,

              SUM(E.EJOB_TOTAL) AS EJOB_TOTAL,

              SUM(R.RJOB_TOTAL) AS RJOB_TOTAL

         FROM PROJECTS   AS P

         LEFT JOIN CJOBS AS C ON C.PROJ_ID=P.PROJ_ID

         LEFT JOIN EJOBS AS E ON E.PROJ_ID=P.PROJ_ID

         LEFT JOIN RJOBS AS R ON R.PROJ_ID=P.PROJ_ID

         GROUP BY P.PROJ_NUMB, P.PROJ_NAME) AS T1;

qq_29171973 2018-07-20

打赏
举报

SELECT DISTINCT
PROJECTS.PROJ_NUMB,
PROJECTS.PROJ_NAME,
CJOBS.CJOB_TOTAL,
T1.TOTAL,
(CJOB_TOTAL-EJOBS.EJOB_TOTAL-T1.TOTAL)AS LIRUN
FROM PROJECTS,CJOBS,EJOBS,
(
SELECT PROJ_ID,SUM(RJOBS.RJOB_TOTAL)AS TOTAL
FROM RJOBS
GROUP BY PROJ_ID
)AS T1
WHERE PROJECTS.PROJ_ID=T1.PROJ_ID AND
PROJECTS.PROJ_ID=CJOBS.PROJ_ID AND
PROJECTS.PROJ_ID=EJOBS.PROJ_ID

我写了个这个兼职的相加是对的了但是公司的没有求和加上重复算出来就不对，下面是结果需要将重复的合并，不重复的相加算出来的利润才对

qq_29171973 2018-07-20

打赏
举报

引用 3 楼 BlueStorm 的回复:

或者写得更通用一点：



SELECT P.PROJ_NUMB, P.PROJ_NAME, 

       ISNULL(CJOBS.CJOB_TOTAL, 0) AS CJOB_TOTAL,

	   ISNULL(EJOBS.EJOB_TOTAL, 0) AS EJOB_TOTAL

	   ISNULL(RJOBS.RJOB_TOTAL, 0) AS RJOB_TOTAL

	FROM PROJECTS

	LEFT JOIN CJOBS ON CJOBS.PROJ_ID=PROJECTS.PROJ_ID

	LEFT JOIN EJOBS ON EJOBS.PROJ_ID=PROJECTS.PROJ_ID

	LEFT JOIN RJOBS ON RJOBS.PROJ_ID=PROJECTS.PROJ_ID

	ORDER BY P.PROJ_ID

提示出错
Engine Error (code = 335544569):
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 4, column 8.
ISNULL.

SQL Error (code = -104):
Invalid token.

----------------------------

Engine Error (code = 335544569):
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, column 19.
=.

SQL Error (code = -104):
Invalid token.

我们表是没有空值的，只有0表示所以这个空值不存在呀