时间戳

首页 > 解决方案 > 从 SQL 中的四个相关表中获取最小值

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

问题描述

我有几个问题，

首先，当我阅读一些文章时，我知道我将在这个问题中使用内部联接来实现 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

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

如果城市 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

*但它不能很好地工作。像下面的照片： 我的 SQL 代码的结果

任何帮助将不胜感激。*

标签： mysqlsql

解决方案

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

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

有关设置，请参阅 Fiddle。

SQL小提琴

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

这给你

结果：

| 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

结果：

|          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

推荐阅读