sql iif 语句连接表问题

Question

我想产生结果:

table name: HWData
policy number:        number of residents:   factor:
100                     2                      1
101                     4                      1.25
102                     7                      2
103                     99                     2
104                     1                      0.85

使用规则:

table name: Tier_Occupancy
number of residents:   factor:
1                       0.85
2                       1  
3                       1.10
4                       1.25
5                       1.5
6 or above              2.0

这是我到目前为止:

SELECT 
HWData.[Policy Number], 
iif( HWData.[Number of Residents] = 1, Tier_Occupancy.Factor,1 ), 
iif( HWData.[Number of Residents] = 2, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 3, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 4, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 5, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] >= 6, Tier_Occupancy.Factor, 1)

FROM HWData 
INNER JOIN Tier_Occupancy ON HWData.[Number of Residents]=Tier_Occupancy.[Number of Residents]

ORDER BY HWData.[Policy Number];

我的问题是它确实显示了结果，但它显示了 1 1 1 1 1 1保单编号 100
它显示 1 1 1 1.25 1 1对于保单编号 101，但我不想要:我只想显示 1 代表保单编号 100，显示 1.25 代表保单编号 101。

我使用的是 2007 Microsoft Access 的 SQL。

Answer 1

替换这个

iif( HWData.[Number of Residents] = 1, Tier_Occupancy.Factor,1 ), 
iif( HWData.[Number of Residents] = 2, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 3, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 4, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] = 5, Tier_Occupancy.Factor, 1), 
iif( HWData.[Number of Residents] >= 6, Tier_Occupancy.Factor, 1)

有了这个

Tier_Occupancy.Factor

连接需要固定。这是您需要使用 IIF 的地方。

ON iif(HWData.[Number of Residents]>5,6,HWData.[Number of Residents]) = Tier_Occupancy.[Number of Residents]

并从 Tier_Occupancy 表的最后一个“居民人数”条目中的最后一个条目中删除“或以上”。那应该是一个数字列。

另外，不要忘记在结果中选择您所说的居民人数列。