使用Join、SQL、Order Categories(按产品）的复杂排序函数'；到期，Opencart

更新

我的最终目标是根据产品的有效期对类别进行排序。因此，产品最接近过期的类别排在第一位，所有产品过期的类别最后一位。感谢Greg，请参阅问题底部的完整答案。

/UPDATE

除了第二行（SELECT MIN（date_expires…关于如何重写这一行有什么想法吗？

 SELECT   c.category_id, 
          name, 
          c.image, 
          description,
          (SELECT MIN(date_expires) 
           FROM     product 
           WHERE date_expires >= NOW() AND 
                 p.product_id = p2c.product_id) AS expiring
FROM category c 
        LEFT JOIN category_description cd 
            ON (c.category_id = cd.category_id) 
        LEFT JOIN product_to_category p2c 
            ON (c.category_id = p2c.category_id) 
        LEFT JOIN product p 
            ON (p2c.product_id = p.product_id) 
WHERE c.parent_id = 0 AND c.status = '1'
GROUP BY c.category_id
ORDER BY expiring

一些样本数据：

category:
category_id = 62
category_id = 64
category_id = 63
product_to_category:
product_id = 43 category_id = 62
product_id = 50 category_id = 63
product_id = 56 category_id = 63
product_id = 58 category_id = 62
product_id = 59 category_id = 63
product_id = 60 category_id = 63
product_id = 61 category_id = 63
product_id = 62 category_id = 63
product_id = 63 category_id = 64
product:
product_id = 43 date_expires = 2012-07-11 20:35:00
product_id = 50 date_expires = 2012-06-29 00:00:00
product_id = 56 date_expires = 2012-07-13 00:00:00
product_id = 58 date_expires = 2012-07-26 15:01:00
product_id = 59 date_expires = 2012-07-16 14:12:00
product_id = 60 date_expires = 2012-07-18 08:15:00
product_id = 61 date_expires = 2012-08-02 13:04:00
product_id = 62 date_expires = 2012-08-24 00:00:00
product_id = 63 date_expires = 2012-07-12 19:16:00

答案我的最终目标是获得

SELECT c.category_id, minDateExpires.Expiring, 
CASE WHEN Expiring > 1 THEN 1 ELSE 2 END as available 
FROM category c
LEFT JOIN product_to_category p2c ON (c.category_id = p2c.category_id)   
LEFT JOIN product p ON (p2c.product_id = p.product_id)   
LEFT JOIN ( 
    SELECT MIN(date_expires) AS expiring, category_id FROM product p join product_to_category p2c on p2c.product_id = p.product_id WHERE date_expires >= GETDATE() GROUP BY category_id
) minDateExpires on p2c.category_id = minDateExpires.category_id
ORDER BY available, expiring  

如果您正在为OpenCart特别查找此项，并且您正在使用Categories模块，请转到catalog/model/controlog/category.php并将函数getCategoriesPag（）编辑为：

    public function getCategoriesPag($data) {
        $query = $this->db->query("SELECT c.category_id, name, c.image, description, minDateExpires.Expiring, CASE WHEN Expiring > 1 THEN 1 ELSE 2 END as available FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id) LEFT JOIN (SELECT MIN(date_expires) AS expiring, category_id FROM product p JOIN product_to_category p2c on p2c.product_id = p.product_id WHERE date_expires >= NOW() GROUP BY category_id) minDateExpires on p2c.category_id = minDateExpires.category_id WHERE c.parent_id = 0 AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' GROUP BY c.category_id ORDER BY available, Expiring, c.sort_order");
        return $query->rows;
    }

您需要将自己的date_expires字段添加到数据库和后端管理面板中。