我有两张桌子,一张叫CarTypes,另一张叫小时工资。
CarType具有以下属性:
Id,
CarTypes,
Rates
HourlyID related to HourlyRates based on HourlyId-ID relationship.
Id is an AutoNumber
CarTypes are Sedan,
Sports Utility,
Stretch Limo 6 Passengers,
Stretch Limo 8 passengers
Stretch Limo 10 Passengers
Stretch Limo 12 Passengers每一种类型的汽车都有自己的费率。
然后,表HourlyRates具有以下属性: ID autoNumber,小时(最小小时为3),最大值为13以上
我们的任务是将每辆车的指定车费乘以所选择的小时数,确定10%的折扣金额,确定(7%的税)的税额,确定小费或小费的数额(20%)。最后,从车费中减去折扣,加上税额,再加上小费来生成总额。
到目前为止,我的计算只显示了两种类型的汽车的所有数值(车费、折扣、小费、总计),即轿车和运动通用汽车。
有人能告诉我我做错了什么吗?
预先谢谢:
SELECT DISTINCT c.carTypes, h.hourly, h.hourly * IIf([CarTypes] = 'Sedan', 55,
IIf([CarTypes] = 'Stretch Limo : 6 Passenger)', 75,
IIf([CarTypes] = 'Stretch Limo : 8 Passenger)', 90,
IIf([CarTypes] = 'Stretch Limo : 10 Passenger)', 95,
IIf([CarTypes] = 'Stretch Limo : 12 Passenger)', 110,
IIf([CarTypes] = 'SportUtilityVehicle)', 110)))))) AS Fare, Fare*10/100 AS Discount, Fare-Discount AS NewFare, NewFare* 7/100 AS Tax, NewFare* 20/100 AS Tip, NewFare+Tax+TIP AS Total
FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID;

发布于 2014-12-11 21:54:06
除了轿车之外,您还以每个)的名义获得了额外的CarType,因此它们从未在嵌套的IIF()条件中捕获。
尝试:
SELECT DISTINCT c.carTypes, h.hourly, h.hourly * IIf([CarTypes] = 'Sedan', 55,
IIf([CarTypes] = 'Stretch Limo : 6 Passenger', 75,
IIf([CarTypes] = 'Stretch Limo : 8 Passenger', 90,
IIf([CarTypes] = 'Stretch Limo : 10 Passenger', 95,
IIf([CarTypes] = 'Stretch Limo : 12 Passenger', 110,
IIf([CarTypes] = 'SportUtilityVehicle', 110)))))) AS Fare, Fare*10/100 AS Discount, Fare-Discount AS NewFare, NewFare* 7/100 AS Tax, NewFare* 20/100 AS Tip, NewFare+Tax+TIP AS Total
FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID;在我看来,SWITCH()函数更容易读懂:
SELECT DISTINCT c.carTypes, h.hourly, h.hourly *
SWITCH([CarTypes] = 'Sedan', 55
,[CarTypes] = 'Stretch Limo : 6 Passenger', 75
,[CarTypes] = 'Stretch Limo : 8 Passenger', 90
,[CarTypes] = 'Stretch Limo : 10 Passenger', 95
,[CarTypes] = 'Stretch Limo : 12 Passenger', 110
,[CarTypes] = 'SportUtilityVehicle', 110
) AS Fare
, Fare*10/100 AS Discount
, Fare-Discount AS NewFare
, NewFare* 7/100 AS Tax
, NewFare* 20/100 AS Tip
, NewFare+Tax+TIP AS Total
FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID;https://stackoverflow.com/questions/27432824
复制相似问题