如何解决IIF (SUM IIF) SQL脚本中的空数据表结果

Question

我有一个Excel报告，我试图用SQL查询在中重新创建。我使用了IIF求和函数，但它不是在特定字段中返回预期的结果(“是”)，而只是返回空白单元格。我的剧本怎么了？

我以前没有使用过这个IIF和函数，所以我需要一些帮助。下面的SQL查询是用示例表和字段名构造的。

SELECT DISTINCT [Table 1].[Paint ID], 
                [Table 1].[Paint Colour], 
                [Table 1].[Production Date], 
                [Table 1].[Price], 
                [Table 1].[VAT], 
                [Table 1].[Total Units],
IIF(Sum(IIF([Table 2].[Company Name] IN("Paintbox Ltd", "ColourSplash plc", "HomeDIY"),1,0))>0,"YES",NULL) AS UK Sellers,
IIF(Sum(IIF([Table 2].[Company Name] IN("BrightWalls", "PerfectHome", "PaintIt"),1,0))>0,"YES",NULL) AS Europe Sellers,
IIF(SUM(IIF([Table 2].[Company Name]="Habari",1,0))>0,"YES",NULL) as Africa Sellers,
IIF(SUM(IIF([Table 2].[Company Name]="Malay Paint",1,0))>0,"YES",NULL) as Asia Sellers
    FROM [Tale 1] LEFT JOIN [Table 2] ON [Table 1].[Paint ID] = [Table 2].[Paint ID]
    GROUP BY [Table 1].[Paint ID], [Table 1].[Paint Colour], [Table 1].[Production Date], [Table 1].[Price], [Table 1].[VAT], [Table 1].[Total Units];

我期待一个栅格为英国卖方，欧洲卖方等沿顶部与‘是’在其中一些，如在excel报告。然而，他们正在空白返回。除此之外，查询正在运行ok，结果网格是我期望它在其布局中的样子，只是在我期望‘是’有空白单元格的地方。

Answer 1

查询中的逻辑是正确的。

我复制了它，修复了语法错误，在表中添加了别名，并删除了DISTINCT关键字，这是无用的，因为您使用了GROUP BY，而这段代码就像预期的那样工作：

SELECT 
  t1.[Paint ID], 
  t1.[Paint Colour], 
  t1.[Production Date], 
  t1.[Price], 
  t1.[VAT], 
  t1.[Total Units],
IIF(Sum(IIF(t2.[Company Name] IN ("Paintbox Ltd", "ColourSplash plc", "HomeDIY"), 1, 0)) > 0, "YES", NULL) AS UK Sellers,
IIF(Sum(IIF(t2.[Company Name] IN ("BrightWalls", "PerfectHome", "PaintIt"), 1, 0)) > 0,"YES", NULL) AS Europe Sellers,
IIF(SUM(IIF(t2.[Company Name] = "Habari", 1, 0)) > 0, "YES", NULL) as Africa Sellers,
IIF(SUM(IIF(t2.[Company Name] = "Malay Paint", 1, 0)) > 0, "YES", NULL) as Asia Sellers
FROM [Table 1] AS t1 LEFT JOIN [Table 2] AS t2 ON t1.[Paint ID] = t2.[Paint ID]
GROUP BY t1.[Paint ID], t1.[Paint Colour], t1.[Production Date], t1.[Price], t1.[VAT], t1.[Total Units];

Answer 2

我建议使用max聚合函数，以便完全删除一组iif语句，例如：

select
    [Table 1].[Paint ID], 
    [Table 1].[Paint Colour], 
    [Table 1].[Production Date], 
    [Table 1].[Price], 
    [Table 1].[VAT], 
    [Table 1].[Total Units],
    max(iif([Table 2].[Company Name] in ("Paintbox Ltd", "ColourSplash plc", "HomeDIY"),"YES",Null)) as [UK Sellers],
    max(iif([Table 2].[Company Name] in ("BrightWalls", "PerfectHome", "PaintIt"),"YES",Null)) as [Europe Sellers],
    max(iif([Table 2].[Company Name]="Habari","YES",Null)) as [Africa Sellers],
    max(iif([Table 2].[Company Name]="Malay Paint","YES",Null)) as [Asia Sellers]
from 
    [Table 1] left join [Table 2] on [Table 1].[Paint ID] = [Table 2].[Paint ID]
group by 
    [Table 1].[Paint ID], 
    [Table 1].[Paint Colour], 
    [Table 1].[Production Date], 
    [Table 1].[Price], 
    [Table 1].[VAT], 
    [Table 1].[Total Units];