完全联接在两种方式上均无效 [英] FULL JOIN doesn't work in two ways

问题描述

我正在尝试用SQL编写FULL JOIN语句，但它的工作效果不尽如人意. 这是我的完整声明

I'm trying to write a FULL JOIN statement in SQL but it doesn't work as fine as I would like. Here is my full statement

 SELECT 
        t.tacNom, bh.heuresChiffrees, 
        SUM(ISNULL(heures,0)) + SUM(ISNULL(minutes,0)/60) as HeuresRealisee, (ISNULL(bh.heuresChiffrees,0) - (SUM(ISNULL(heures,0)) + (SUM(ISNULL(minutes,0))/60))) as Solde,
        (SUM(DISTINCT minutes)%60) as Soldeminutes
    FROM tbl_BalanceHeures bh
            FULL OUTER JOIN tbl_HeuresTimbrage_new ht ON  ht.idProjet = bh.proIdProjetExterne 
            AND ht.idxTache = bh.idxTache 
            AND ht.idxDep= bh.idxDepartement
            INNER JOIN tbl_Taches t on t.idTache = bh.idxTache
            WHERE proIdProjetExterne = '00Z 000104' AND bh.idxDepartement = 184
    GROUP BY  t.tacNom ,bh.heuresChiffrees

这是声明的结果

idTache heuresChiffrees HeuresRealisee  Solde
332              25                 0        25  
330              50                 0        50
327             100                42    58
331             100                23    77

这是我的表tbl_Balance和语句中的记录

Here are the records in my table tbl_Balance and the statement

select t.idTache, bh.heuresChiffrees
from tbl_BalanceHeures bh
INNER JOIN tbl_Taches t on t.idTache = bh.idxTache
WHERE proIdProjetExterne= '00Z 000104' AND bh.idxDepartement = 184

idTache heuresChiffrees
330      50
331     100
327     100
332      25

这是tbl_HeuresTimbrage中的记录和该语句

Here are the records in tbl_HeuresTimbrage and the statement

 Select t.idTache, ht.heures as heuresRealisees
 From tbl_HeuresTimbrage_new ht
  INNER JOIN tbl_Taches t on t.idTache = ht.idxTache
 WHERE idProjet= '00Z 000104' AND ht.idxDep = 184

  idTache   heuresRealisees
    327               32
    331               23
    327               10
    334               4

因此，我希望在我的FULL JOIN语句中找到

So, In my FULL JOIN Statement I am expected to find

IdTache  heuresChiffrees Heures Realisees
327       100           10
330       50          NULL (or 0)
331       100           23
332       25          NULL (or 0)
334     NULL(or 0)    4

我尝试将WHERE子句放在Full JOIN中，但是它不起作用

I've tried to put my WHERE clause in the Full JOIN but it doesn't work

推荐答案

最可能是因为您的WHERE子句.它作用于合并结果，因此，如果A面为空，则将消除这些行.

It's most likely because of your WHERE clause. It is acting on the joined result, and so if the A side is null, it will eliminate those rows.

您可以将其更改为联接的条件，如下所示:

You can change it to be a condition of the join instead, as so:

FROM A 
FULL OUTER JOIN B  
ON  B.idProjet = A.proIdProjetExterne 
AND B.idxTache = A.idxTache 
AND B.idxDep= A.idxDepartement
AND A.proIdProjetExterne =  'xxx' 
AND A.idxDepartement = 184

但是，在没有看到您的实际数据或架构的情况下，这有点猜测...

However, without seeing your actual data or schema, this is a bit of a guess...

这篇关于完全联接在两种方式上均无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！