mysql - 如何组合这个 MySQL 查询？

Question

I have 2 tables like this:

table:prices
id, project_id, real_min_price, real_max_price
1 | 100100 | 500  | 2000 
2 | 100100 | 900  | 3000 
3 | 100100 | 2500 | 3200
4 | 100100 | 320  | 3900

table:gifts
id, project_id, min_price, max_price, gift
1 | 100100 | 0    | 1000 | 10
2 | 100100 | 1001 | 2000 | 20
3 | 100100 | 2001 | 3000 | 30
4 | 100100 | 3001 | 4000 | 40
5 | 100100 | 4001 | 5000 | 50
6 | 100100 | 5001 | 6000 | 60

$ID = 100100；

//查找最高价格

SELECT MAX(real_max_price) FROM `prices` WHERE project_id='$ID';

$MAX_PROJECT_PRICE = $dbo->getOne();

-- 返回 3900

//找到该值的最小-最大列之间的限制行

SELECT gift FROM `gifts` WHERE project_id='$ID' 
AND max_price>='$MAX_PROJECT_PRICE' ORDER BY max_price ASC LIMIT 1;

$MAX_GIFT = $dbo->getOne();

-- 创建礼品表的第 4 行并返回 40

//删除高于 MAX_GIFT 值的其他礼品行

DELETE FROM `gifts` WHERE project_id='$ID' AND gift>'$MAX_GIFT';

-- 删除第 5 行和第 6 行。

在这种情况下

它会发现最高价格为“3900”，因此，第 5 行和第 6 行的礼品表将被删除。

但是这种方式真的很糟糕，它应该在一个查询中完成，但是如何实现？

Answer 1

Okay you can except delete query, at least maybe we can combine first 2 queries to find MAX_GIFT value.

要结合您可以执行的前两个查询。

查询

SELECT
    gift
FROM
    gifts
WHERE
    project_id = 100100 
    AND max_price >= (SELECT MAX(real_max_price)
                      FROM prices
                      WHERE project_id = 100100;)
ORDER BY
    max_price ASC
LIMIT 1;

并将所有三个查询合而为一。

查询

DELETE FROM
  gifts
WHERE
     project_id = 100100
  AND
     gift > (SELECT
               gift
             FROM
               gifts
             WHERE
                project_id = 100100 
              AND
                max_price >= (SELECT
                                MAX(real_max_price)
                              FROM
                                prices
                              WHERE   
                                 project_id = 100100;
                             )
              ORDER BY
                max_price ASC
              LIMIT 1
           )