选择最高薪水

小编典典

选择最高薪水

sql

假设有工资表：

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，是否可以尝试使用“ Default”？

阅读 185

2021-04-28

共1个答案

小编典典

如果仅通过1个查询未找到$ 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了Default，并1用于任何其他用户。然后，您可以简单地执行以下操作：

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

当然，这是相同的解决方案，但比依赖默认值名称中的特殊字符更好。

2021-04-28