左连接语句为包含数据的行/列返回意外的空值

Question

我要对两个表编写查询。有些列可以在其中一个表中找到，而有些列是计算出来的。

为了清楚起见，我将我的查询复制到下面:

select field_a,
       cast(field_b as int),
       field_c,
       field_d,
       Year,
       coalesce( cast(field_e as float),0) America_spend,
       sum( cast(field_e as float), 0) as America_spend,
       coalesce( cast(field_e as float)/ sum( cast( field_e as float)) over(partition by Year) as total_spend
from table_a
left join table_b on
    table_a.flield_a = table_b.field_a1 and 
    table_a.flield_b = table_b.field_b1 and 
    table_a.Year = table_b.Year
group by field_a,
         field_b,
         Year

我有这样的表

表答:

|field_a|field_b|field_c|field_d|Year|field_f|field_g|field_h
|data   |   1   | data  |  data |2014| data  | data  | data
|data   |   1   | data  |  data |2014| null  | data  | data
|0      |   1   | data  |  data |2014| data  | data  | data
|data   |   1   | data  |  data |2014| null  | data  | data
|0      |   1   | data  |  data |2014| data  | data  | data

表b:

|field_a1|field_b1|Year|field_c1|field_j
|null    |   1   |2014| data   | data
|data    |   1   |2015| data   | data
|null    |   0   |2014| data   | data
|data    |   1   |2015| data   | data
|null    |   0   |2014| data   | data

我遇到的问题是，"总支出列"中的一些值被分配为null值。总支出是每年计算的，这个字段不应该为空。同样，这两个表中的year列都不包含空值。但是由于某种原因，当我运行查询时，我得到的结果中year列中的一些行为空值。这种情况不应该发生。大多数结果符合我的预期，但也有一些不符合。

我猜这与field_b中的一些行为空并转换为0的事实有关，但为什么这很重要?

我更新了表和查询，以更准确地反映数据库的结构。

是查询运行，我没有命名冲突。

Answer 1

@SeanLange的评论是你预期结果中最有可能出现的问题。即NULL不等于NULL (NULL <> NULL)Null为sql中的"unknown"值，且两个未知数不相等

但是如果您想将它们作为相同的情况匹配在一起，则可以消除NULL。只需使用COALESCE()或ISNULL()，并在on条件的两侧提供相同的默认值，并确保您的默认值不在数据集中表示，否则您将得到不希望的结果。

DECLARE @TableA AS TABLE (FieldA VARCHAR(5),FiledB INT,Yr INT)
DECLARE @TableB AS TABLE (FieldA VARCHAR(5),FiledC INT,Yr INT)
INSERT INTO @TableA (FieldA,FiledB,Yr)
VALUES (null,1,2014),('data',1,2015),(null,1,2014),('data',1,2015),(null,1,2014)
INSERT INTO @TableB (FieldA,FiledC,Yr)
VALUES (null,1,2014),('data',1,2015),(null,1,2014),('data',1,2015),(null,1,2014)
SELECT *
FROM
    @TableA a
    LEFT JOIN @TableB b
    ON COALESCE(a.FieldA,'NULLVALUE') = COALESCE(b.FieldA,'NULLVALUE')
    AND a.Yr = b.Yr

您提供给我们的特定示例数据集重复FieldA到Yr组合，因此结果有点奇怪，但它仍然有效。