全外连接问题 [英] Full outer join issue

问题描述

你好，


我对此有点新意，所以我希望我已经正确地解释了它。请询问您是否需要任何其他信息。这是我正在经历的问题的简化版本：


我有两个财务表。一个表（ACTUAL）包含雇员列表及其各个时期的实际支出金额。另一个表（BUDGET）包含雇员列表及其各个时期的预算支出金额。 IE浏览器。两个表都有标识列：empno，amount和period。


我想做一个简单的查询，我将显示员工姓名，总实际金额，以及每位员工的总预算金额。如果预算中包含一个有金额的员工，但实际情况并不是我想要显示它，并在实际金额列中留空或为零。表。


我原本认为以下代码可以正常工作：

展开 | 选择 | 换行 | 行号

解决方案

试试这个：


[PHP]

SELECT a.EMPNO，sum（isnull（ a.amount，0）AS actamount，sum（isnull（b.amount，0）AS budamount

来自实际a

LEFT JOIN BINGET b on a.EMPNO = b .EMPNO

GROUP BY a.EMPNO [/ PHP]

试试这个：



[PHP]

SELECT a.EM PNO，sum（isnull（a.amount，0）AS actamount，sum（isnull（b.amount，0）AS budamount

来自实际a

LEFT JOIN BINGET b ON a.EMPNO = b.EMPNO

GROUP BY a.EMPNO [/ PHP]

非常感谢;我肯定会在明天的工作中尝试一下。

一个问题，因为它是一个左连接，如果另一个表包含ACTUAL没有的数据怎么办？


提前感谢，

FD

我假设两个表中都存在EMPNO，如果不存在，我会这样做：

SELECT a.EMPNO，sum（isnull（a.amount，0）AS actamount，sum（isnull（b.amount，0）AS budamount

来自实际a

LEFT JOIN BINGET b on a.EMPNO = b.EMPNO

GROUP BY a.EMPNO

UNION

SELECT b.EMPNO，sum（isnull（a.amount，0）AS actamount，sum（isnull（b.amount，0）AS budamount

FROM ACTUAL a

RIGHT JOIN BUDGET b ON a.EMPNO = b.EMPNO

GROUP BY a.EMPNO

祝你好运。

Hi there,

I''m kinda new to this so I hope I''ve explained it properly. Please ask if you need any additional info. Here is a simplified version of the issue I''m experiencing:

I have two financial tables. One table ("ACTUAL") contains a list of employees and their actual spending amounts for various periods. The other table ("BUDGET") contains a list of employees and their budget spending amounts for various periods. Ie. Both tables have idential columns: empno,amount and period.

I''d like to do a simple query where I will get the Employee name displayed, the total actual amount, and the total budget amount for each employee. Where the budget contains an employee with an amount but the actuals don''t I would like to still display it and put a blank or zero in the amount column for "ACTUAL" table.

I would have thought the following code would work:

Expand|Select|Wrap|Line Numbers

解决方案

Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO[/PHP]

Try this:


[PHP]
SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO[/PHP]

Thanks a lot; I''ll be sure to try that at work tomorrow.
One question, since it is a left join, what if the other table contains data that the ACTUAL does not?

thanks in advance,
FD

I assume that EMPNO exists in both tables, if not I would do following:

SELECT a.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
LEFT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO
UNION
SELECT b.EMPNO, sum(isnull(a.amount,0) AS actamount, sum(isnull(b.amount,0) AS budamount
FROM ACTUAL a
RIGHT JOIN BUDGET b ON a.EMPNO=b.EMPNO
GROUP BY a.EMPNO

Good Luck.

这篇关于全外连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！