首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >分组依据中的MySQL排序依据

问分组依据中的MySQL排序依据
EN

Stack Overflow用户
提问于 2015-05-28 20:13:14
回答 1查看 1.2K关注 0票数 2

我知道Stackoverflow上已经广泛地介绍了这一点,但不幸的是,我对我的特定代码的实现一无所知。

基本上,我有一个满是产品的列表页面,某些产品会列出两次、三次甚至更多次。有一个非常好的理由,但不管怎样。

我想按产品名称分组,这样具有相同名称的各种产品就不会在列表页面上多次出现。但是我希望价格最低的那个是被group by选中的那个。代码如下:

代码语言:javascript
复制
$listing_sql = "SELECT DISTINCT " . $select_column_list . "
p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price,  p.products_tax_class_id,  
pd.products_description, pd.products_name, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,  
IF(s.status =1, s.specials_new_products_price,
p.products_price) as final_price, p.products_sort_order,
p.product_is_call, p.product_is_always_free_shipping,
p.products_qty_box_status";

$listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .

" LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
" LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id =
pd.products_id" .
" JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id =
p2c.products_id" .
($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on
p.products_id = p2a.products_id" .
" JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id =
po.products_options_id" .
" JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on
p2a.options_values_id = pov.products_options_values_id" .
(defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " .
TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id =
p2as.products_id " : "") : '');

$listing_sql .= " WHERE p.products_status = 1
and p.products_quantity > 0
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$filter .
" group by pd.products_name " .
$having .
$alpha_sort;

该组在接近尾声时实现:

代码语言:javascript
复制
" group by pd.products_name " .

工作正常。但很明显,它会根据找到的第一个版本进行分组,因此,如果它不是进入数据库的产品的第一个版本,那么价格最低的那个就会被埋没。与查询中一样,价格列是:

代码语言:javascript
复制
p.products_price

我在这里查看并尝试实现了一些示例:Select the 3 most recent records where the values of one column are distinct

我就是不能正确理解它们--我对MySQL查询的理解实在是太差了。任何帮助都将不胜感激。

更新:

代码语言:javascript
复制
SELECT  DISTINCT  p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price,  p.products_tax_class_id, 
pd.products_description, pd.products_name, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,     
IF(s.status =1, s.specials_new_products_price, p.products_price) as 
final_price, p.products_sort_order, p.product_is_call, 
p.product_is_always_free_shipping, p.products_qty_box_status

FROM zen_products p
LEFT JOIN zen_specials s on p.products_id =   
s.products_id
LEFT JOIN zen_products_description pd on p.products_id = pd.products_id
JOIN zen_products_to_categories p2c on p.products_id = p2c.products_id
WHERE p.products_status = 1
and p.products_quantity > 0
and pd.language_id = '1'
and p2c.categories_id = '3'

GROUP BY pd.products_name
EN

回答 1

Stack Overflow用户

发布于 2015-05-28 20:30:00

我不会费力地讨论这些问题,但一般的解决方案如下:

代码语言:javascript
复制
SELECT x.* -- DISTINCT would be redundant in this context.
  FROM my_table x
  JOIN 
     ( SELECT grouping_column
            , MIN(ordering_column) n 
         FROM my_table 
        GROUP 
           BY grouping_column
     ) y
    ON y.grouping_column = x.grouping_column
   AND y.n = x.ordering_column;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/30506271

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档