首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >我的计算得到了所需结果的一半。你知道我做错了什么吗?

问我的计算得到了所需结果的一半。你知道我做错了什么吗?
EN

Stack Overflow用户
提问于 2014-12-11 21:35:49
回答 1查看 35关注 0票数 1

我有两张桌子,一张叫CarTypes,另一张叫小时工资。

CarType具有以下属性:

代码语言:javascript
复制
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%)。最后,从车费中减去折扣,加上税额,再加上小费来生成总额。

到目前为止,我的计算只显示了两种类型的汽车的所有数值(车费、折扣、小费、总计),即轿车和运动通用汽车。

有人能告诉我我做错了什么吗?

预先谢谢:

代码语言:javascript
复制
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;

​

​

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-12-11 21:54:06

除了轿车之外,您还以每个)的名义获得了额外的CarType,因此它们从未在嵌套的IIF()条件中捕获。

尝试:

代码语言:javascript
复制
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()函数更容易读懂:

代码语言:javascript
复制
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;
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/27432824

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档