假设工资表:
name lowhours highhours wage
Default 0.0 40.0 100
Default 40.0 50.0 150
Default 50.0 70.5 154
Default 70.5 100.0 200
Brian 0.0 40.0 200
Brian 40.0 50.0 250
Brian 50.0 60.0 275
Brian 60.0 70.0 300
Brian 70.0 80.0 325
Brian 80.0 9999.0 350
Chad 0.0 40.0 130
Chad 40.0 9999.0 170
我目前正在使用此代码(AutoIT 脚本:
func getCurrentWage($Employee, $Hour_number)
Dim $row
Local $Wage = 0
Local $Found = "found"
_SQLite_QuerySingleRow(-1, "SELECT wage FROM wagetable WHERE name LIKE " & _SQLite_Escape($Employee) & " AND " & _SQLite_Escape($Hour_number) & " BETWEEN lowhours AND highhours;", $row)
if @error then
_SQLite_QuerySingleRow(-1, "SELECT wage FROM wagetable WHERE name LIKE 'Default' AND " & _SQLite_Escape($Hour_number) & " BETWEEN lowhours AND highhours;", $row)
$Found = "not found"
endif
If ($row[0] == "") Then Msgbox(0,0,"Error getCurrentWage")
$Wage = $row[0]
Debug("Wage='" & $Wage & "' because " &$Employee&" was "& $Found& " -- and Hours Elapsed is " & $Hour_number, true)
return $Wage
EndFunc
因此,如果 Hour_number 介于低小时或高小时之间,这两个查询是完美的。
我需要某种查询,它基本上会执行以下操作:
SELECT wage from wagetable WHERE name LIKE $Employee AND max(highhours)
然后只需重复“默认”即可,因为找不到该员工。
额外:如果仅通过 1 次查询未找到 $Employee,是否可以尝试“默认”?
如果仅通过 1 次查询未找到 $Employee,是否可以尝试“默认”?
SELECT name, wage, highhours
FROM wagetable
WHERE name like 'Brian' OR
name like 'Default'
ORDER BY name,
highhours desc
该查询在查询时有效Brian
,但要使其适用于任何名称Default
应存储在数据库中以特殊字符开头_-+@
,因为数字和字母在排序中排在第一位。
另一种方法是您在表中创建另一列,为了我们的论证,称为priority
应该有这个值0
for Default
and 1
对于任何其他用户。然后你可以简单地这样做:
SELECT name, wage, highhours
FROM wagetable
WHERE name like 'Brian' OR
name like 'Default'
ORDER BY priority desc,
highhours desc
当然,这是相同的解决方案,但它是比依赖默认值名称中的特殊字符更好的方法。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)