什么是最快和/或最简单的方法来做到这一点？ [英] What would be the fastest and/or simplest way to do this?

问题描述

我有下面的表格和查询：

I have the following table and query:

CREATE  TABLE IF NOT EXISTS `sizes` (
  `id_size` INT NOT NULL ,
  `cm_min` INT NOT NULL ,
  `cm_max` INT NOT NULL ,
  PRIMARY KEY (`id_size`) )
ENGINE = InnoDB;

insert into sizes values
(1,          56,        59),
(2,          63,        67),
(3,          70,        74),
(4,          76,        79),
(5,          83,        86),
(6,          60,        62),
(7,          12,        14);

  SELECT * FROM sizes
  WHERE cm_min >= 13 AND cm_min <= 66 OR
  cm_max >= 13 AND cm_max <= 66
  AND cm_min =
    (SELECT min(cm_max) FROM sizes
    WHERE (cm_min >= 13 AND cm_min <= 66) OR
    (cm_max >= 13 AND cm_max <= 66))
UNION
  SELECT * FROM sizes
  WHERE cm_min >= 13 AND cm_min <= 66 OR
  cm_max >= 13 AND cm_max <= 66
  AND cm_min =
    (SELECT min(cm_min) FROM sizes
    WHERE (cm_min >= 13 AND cm_min <= 66) OR
    (cm_max >= 13 AND cm_max <= 66))
ORDER BY cm_max ASC;

在SqlFiddle中查看

和66，你可以看到。
我需要向这个查询添加两个下列条件：

Here is an example with the values 12 and 66 as can you see. I need to add to this query the two next conditions:

1. 如果引入值的范围是最小的所有
   表，查询只返回具有
   最小值的行的数据。例如：如果我有值9和10，
   结果将是id_size 7，因为它是一个具有较小的
   数字。

1. If the range of the introduced values is the smallest of all the table, the query only returns the data of the row that has the smallest value. For example: If I have the values ​​9 and 10, the result will be the id_size 7 because it is the one with the smaller numbers.

如果引入值的范围是所有
表中最大的，则查询仅返回具有
最大值的行的数据。例如：如果我有值112和113，
结果将是id_size 5，因为它是具有较大
数字的那个。

If the range of the introduced values is the largest of all the table, the query only returns the data of the row that has the largest value. For example: If I have the values 112 and 113, the result will be the id_size 5 because it is the one with the larger numbers.

---

您是否知道如何将上述条件添加到我的查询中？
我可以简化吗？

---

Would you know how to add the above conditions to my query? Can I simplify it?

推荐答案

请注意订单的重要性，@ v1必须是范围最小值，@ v2最大值。

This should do the trick. Note that the order is significant, @v1 must be the range minimum, @v2 the maximum.

SET @v1=112, @v2=113;

SELECT *
FROM   sizes
WHERE  (
       cm_min BETWEEN @v1 and @v2
       AND
       cm_max BETWEEN @v1 and @v2
       )
OR
       cm_min = (
         SELECT MIN(cm_min)
         FROM   sizes
         WHERE  (@v1+@v2) <= (cm_min + cm_max)
         )
UNION
SELECT *
FROM   sizes
WHERE  (
       cm_min BETWEEN @v1 and @v2
       OR
       cm_max BETWEEN @v1 and @v2
        )
OR
       cm_max = (
         SELECT MAX(cm_max)
         FROM   sizes
         WHERE  (@v1+@v2) >= (cm_min + cm_max)
         )
ORDER BY cm_max ASC;
;

这篇关于什么是最快和/或最简单的方法来做到这一点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！