SQL子查询-如何查找最小值 [英] SQL Sub-Query - how to find a min value

问题描述

我创建了如下子查询

select hospital.hospitalcode, name, wardno, annualbudget
from hospital, ward
where hospital.hospitalcode = ward.hospitalcode

我要回答的问题是:考虑到所有医院病房，哪个医院的病房年度预算最低?您应该显示医院代码及其名称，病房号码及其年度预算.

The question I am trying to answer is this: Taking account of all hospital wards, which hospital has the ward with the lowest annual budget? You should display the hospital code and its name, the ward number and its annual budget.

如何找到该问题的单个条目?我意识到我需要使用MIN，但在使用多个列名时不知道在哪里使用

How would I find a single entry for this question? I realise I need to use MIN but do not know where to use this when using multiple column names

推荐答案

更有效的方法通常是使用解析函数

The more efficient approach is generally to use analytic functions

SELECT hospitalcode,
       name,
       ward,
       annualbudget
  FROM (SELECT h.hospitalcode,
               h.name,
               w.wardno,
               w.annualbudget,
               rank() over (order by w.annualbudget asc) rnk
          FROM hospital h
               JOIN ward w
                 ON (h.hospitalcode = w.hospitalcode))
 WHERE rnk = 1

不过，您也可以使用子查询

You can also, though, use a subquery

SELECT h.hospitalcode,
       h.name,
       w.wardno,
       w.annualbudget
  FROM hospital h
       JOIN ward w
         ON (h.hospitalcode = w.hospitalcode)
 WHERE w.annualbudget = (SELECT MIN(annualbudget)
                           FROM ward)

如果有多个病房的预算最低，则这两种方法都将返回多行.使用解析函数方法，如果您想每次都精确返回1行，则可以使用row_number函数而不是rank任意打破平局.

Both of these methods will return multiple rows if there are multiple wards that are tied for the lowest budget. With the analytic function method, you can use the row_number function rather than rank to arbitrarily break the tie if you want to return exactly 1 row every time.

这篇关于SQL子查询-如何查找最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！