代码之家 › 专栏 › 技术社区 › ParoX

选择最高工资

sqlite mysql sql

ParoX · 技术社区 · 14 年前

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

SELECT wage from wagetable WHERE name LIKE $Employee AND max(highhours)

再重复一遍“默认”就是找不到员工。

1 回复 | 直到 14 年前

mhitza Federico Taschin 14 年前

如果只有一个查询未找到$Employee,是否可以尝试使用“Default”?

  SELECT name, wage, highhours
    FROM wagetable
   WHERE name like 'Brian' OR
         name like 'Default'
ORDER BY name,
         highhours desc

Brian ,但对于任何一个名字 Default 应该以特殊字符开始存储在数据库中 _-+@ ,因为在排序中数字和字母排在第一位。

另一种方法是为表创建另一列,根据我们的参数,称为 priority 哪个应该有价值 0 对于和 1 对于任何其他用户。然后你可以简单地做:

  SELECT name, wage, highhours
    FROM wagetable
   WHERE name like 'Brian' OR
         name like 'Default'
ORDER BY priority desc,
         highhours desc