mysql - 从 SQL 中的四个相关表中获取最小值

Question

我有几个问题，

首先，当我阅读了一些文章时，我知道我将在这个问题中使用 inner Join 来实现 SQL 代码。我有以下表格:

医院表:

    id  |  hospital_name | adress  | city_id
   -------------------------------------------
    1   | pars           |   55,6LA|   2
    2   | ghaem          |   12.9GI|   2
    3   | Mehr           |   632.Sp|   2
    4   | Erfan          |   21,6UJ|   3
    5   | Razavi         |   31.jjI|   3
    6   | Mohab          |   65.Spi|   1

城市表:

    id  |  cityname 
   --------------------
    1   | LosAngels
    2   | NewYork
    3   | Colifornia

治疗表:

    id  |  TreatmentName 
   ----------------------
    1   | nose surgery   
    2   | Orthopedic  
    3   | Knee joint replacement

治疗价格表:

    id  |  Treatments_id | Hospital_id  | Price
   ------------------------------------------
    1   |       1        |       1      |   700
    2   |       1        |       6      |   800
    3   |       1        |       4      |   900
    4   |       2        |       1      |   500
    5   |       2        |       2      |   700
    6   |       2        |       3      |   300
    7   |       3        |       1      |   600
    7   |       3        |       2      |   450

我的问题是如何按城市代码找到每家医院的最低治疗价格。

我需要类似下面的表格 View 如果城市ID是2:

    price  |  hospital_name | treatment_name| 
   -------------------------------------------
    700    | pars           |   nose surgery   
    300    | Mehr           |   Orthopedic     
    450    | ghaem          |   Knee joint replacement     

这是我的SQL代码:

SELECT Min(treatment_prices.dollar) AS price, 
       hospitals.name_en            AS hname, 
       treatments.title_en          AS title 
FROM   treatment_prices 
       INNER JOIN hospitals 
               ON hospitals.id = treatment_prices.hospital_id 
       INNER JOIN treatments 
               ON treatments.id = treatment_prices.treatment_id 
WHERE  hospitals.city_id = 2 
GROUP  BY treatment_prices.treatment_id 

*但是效果不太好。就像下面的照片:

任何帮助将不胜感激。*

Answer 1

您正在寻找某个城市哪家医院的哪种手术最便宜。

在 MySQL 5.x 中，您必须使用一些内部子查询来查找医院最便宜的程序，然后再次将其连接回表中以获得所需的数据。

请参阅 Fiddle 进行设置。

SQL Fiddle

MySQL 5.x:

首先，您想要找到最便宜的手术价格，并按您所需的城市和该城市的医院进行筛选。

查询 1:

SELECT tp.Treatments_ID, min(tp.Price) AS price
FROM treatment_prices tp
INNER JOIN Hospital h ON tp.Hospital_ID = h.ID
  INNER JOIN Cities c ON h.city_id = c.id
    AND c.ID = 2
GROUP BY tp.Treatments_ID 

这给了你

<强> Results :

| Treatments_ID | price |
|---------------|-------|
|             1 |   700 |
|             2 |   300 |
|             3 |   450 |

现在，您可以使用它作为子查询来再次链接回主表以检索您要查找的数据。

查询 2:

SELECT t2.TreatmentName, h2.Hospital_Name, s1.Price
FROM (
    SELECT tp.Treatments_ID, min(tp.Price) AS price
    FROM treatment_prices tp
    INNER JOIN Hospital h ON tp.Hospital_ID = h.ID
      INNER JOIN Cities c ON h.city_id = c.id
        AND c.ID = 2
    GROUP BY tp.Treatments_ID 
) s1
INNER JOIN treatment_prices tp2 ON s1.Treatments_ID = tp2.Treatments_ID
    AND s1.price = tp2.Price
INNER JOIN Hospital h2 ON tp2.Hospital_ID = h2.ID
INNER JOIN Treatments t2 ON tp2.Treatments_ID = t2.ID

<强> Results :

|          TreatmentName | Hospital_Name | Price |
|------------------------|---------------|-------|
|           nose surgery |          pars |   700 |
|             Orthopedic |          Mehr |   300 |
| Knee joint replacement |         ghaem |   450 |

在 MySQL 的更高版本或任何其他允许窗口函数的 SQL 语言中，这要容易得多。

MySQL 8+:

SELECT s1.TreatmentName, s1.Hospital_Name, s1.Price
FROM (
    SELECT t.TreatmentName, h.Hospital_Name, tp.Price
        , ROW_NUMBER() OVER (PARTITION BY tp.Treatments_ID ORDER BY tp.Price) AS rn
    FROM treatment_prices tp 
    INNER JOIN Hospital h ON tp.Hospital_ID = h.ID
    INNER JOIN Cities c ON h.city_id = c.id
            AND c.ID = 2
    INNER JOIN Treatments t ON tp.Treatments_ID = t.ID
) s1
WHERE rn = 1
;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9b28f7543c3b127990654ad9e401c11a