php - 在 MySQL 中选择第一个大于 x 的值

Question

我正在使用 Codeigniter 开发保险比较平台。我有一个看起来像这样的包表:

+----------+--------------+------------+---------------------+-------------+
| tpckg_id | tpckg_people | tpckg_days | tpckg_policy_amount | tp_name     |
+----------+--------------+------------+---------------------+-------------+
|       21 | individual   | 10         | 1000                | Test25      |
|       22 | family       | 7          | 2000                | Testing     |
|       23 | individual   | 20         | 600                 | TPL Care    |
|       24 | family       | 10         | 1000                | TPL Care    |
|       25 | individual   | 15         | 650                 | Travel Care |
|       26 | family       | 10         | 1100                | TPL Care    |
|       27 | individual   | 7          | 500                 | TPL Care    |
|       28 | family       | 7          | 1200                | Travel Care |
|       29 | family       | 10         | 500                 | TPL Care 2  |
|       30 | individual   | 11         | 500                 | TPL Care    |
+----------+--------------+------------+---------------------+-------------+

现在，我正在使用此查询获取数据:

$this->db->select('travel_packages.tpckg_id, travel_packages.tpckg_people, travel_packages.tpckg_days, travel_packages.tpckg_policy_amount, travel_products.tp_name');

    $this->db->from('travel_packages');
    $this->db->join('travel_products', 'travel_products.tp_id = travel_packages.tpckg_products_id')

    $this->db->where('travel_packages.tpckg_people', 'Individual');
    $this->db->where('travel_packages.tpckg_days >= 10');

    $this->db->group_by('travel_packages.tpckg_policy_amount'); 
    $this->db->order_by('travel_packages.tpckg_policy_amount', 'ASC');

正如预期的那样，返回所有具有 tpckg_people = individual 和 tpckg_days >= 10 的记录像这样:

+----------+--------------+------------+---------------------+-------------+
| tpckg_id | tpckg_people | tpckg_days | tpckg_policy_amount | tp_name     |
+----------+--------------+------------+---------------------+-------------+
|       21 | individual   | 10         | 1000                | Test25      |
|       23 | individual   | 20         | 600                 | TPL Care    |
|       25 | individual   | 15         | 650                 | Travel Care |
|       30 | individual   | 11         | 500                 | TPL Care    |
+----------+--------------+------------+---------------------+-------------+

如果没有与用户输入的天数相同，我希望它返回的只是第一个更大的天数。例如，如果用户输入 9 作为天数，而我没有包含 9 天的包裹，它应该返回每个产品的第一个更大的天数。

像这样:

+----------+--------------+------------+---------------------+-------------+
| tpckg_id | tpckg_people | tpckg_days | tpckg_policy_amount | tp_name     |
+----------+--------------+------------+---------------------+-------------+
|       21 | individual   | 10         | 1000                | Test25      |
|       25 | individual   | 15         | 650                 | Travel Care |
|       30 | individual   | 11         | 500                 | TPL Care    |
+----------+--------------+------------+---------------------+-------------+

请问有什么帮助吗？提前致谢。 :-)

Answer 1

这是一个原始查询...

Select x.* 
  From travel_packages x
  Join
     ( Select tp_name name
            , tpckg_people people
            , min(tpckg_days) min_days 
         from travel_packages 
        where tpckg_people = 'individual' 
          and tpckg_days >= 9 
        group 
           by name
            , people
     ) y
    On y.name = x.tp_name
   And y.people = x.tpckg_people
   And y.min_days = x.tpckg_days;