Sql order by 和 group BY一起使用时需要注意

1.ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中。

 

SELECT * FROM (SELECT * FROM (
SELECT
    `c`.`product_id`,
    `c`.`dc_code`,
    c.product_name AS goods_name,
    c.seo_name AS product_name,
    `c`.`default_img`,
    `c`.`product_jifen`,
    `c`.`product_code`,
    `c`.`colorname`,
    c.status AS bstatus,
    `c`.`iszhekou`,
    `c`.`stock`,
    `c`.`zhekou_jifen`,
    `c`.`cat_id`,
    `c`.`brand_name`,
    `a`.`business_id`,
    a.status AS STATUS,
    a.add_time AS online_time,
    `a`.`add_time`,
    `a`.`istop`,
    a.stock AS business_stock,
    `a`.`jifen`,
    `a`.`price`,
    `a`.`is_discount`,
    `a`.`discount_jifen`,
    `a`.`start_time`,
    `a`.`end_time`,
    `s`.`thumb_note`,
    `s`.`mobile_thumb_note`,
    `a`.`priority`
  FROM
    newerp_business_lipin a
    INNER JOIN `newerp_lipin` `c`
      ON `c`.`product_id` = `a`.`product_id`
    INNER JOIN `newerp_own_supplier` `s`
      ON `s`.`id` = `c`.`supplier_id`
  WHERE (
      a.status = 1
      AND a.business_id = 7
      AND c.product_name LIKE '%婵%'
    )
    AND `a`.`status` = 1
) `t1`
GROUP BY `t1`.`product_code`,`t1`.`priority`
ORDER BY 
`t1`.`priority` DESC
) `t2` 
GROUP BY `t2`.`product_code`

2.子查询加上 limit 关键字

 

SELECT * FROM (
SELECT
    `c`.`product_id`,
    `c`.`dc_code`,
    c.product_name AS goods_name,
    c.seo_name AS product_name,
    `c`.`default_img`,
    `c`.`product_jifen`,
    `c`.`product_code`,
    `c`.`colorname`,
    c.status AS bstatus,
    `c`.`iszhekou`,
    `c`.`stock`,
    `c`.`zhekou_jifen`,
    `c`.`cat_id`,
    `c`.`brand_name`,
    `a`.`business_id`,
    a.status AS STATUS,
    a.add_time AS online_time,
    `a`.`add_time`,
    `a`.`istop`,
    a.stock AS business_stock,
    `a`.`jifen`,
    `a`.`price`,
    `a`.`is_discount`,
    `a`.`discount_jifen`,
    `a`.`start_time`,
    `a`.`end_time`,
    `s`.`thumb_note`,
    `s`.`mobile_thumb_note`,
    `a`.`priority`
  FROM
    newerp_business_lipin a
    INNER JOIN `newerp_lipin` `c`
      ON `c`.`product_id` = `a`.`product_id`
    INNER JOIN `newerp_own_supplier` `s`
      ON `s`.`id` = `c`.`supplier_id`
  WHERE (
      a.status = 1
      AND a.business_id = 7
      AND c.product_name LIKE '%婵%'
    )
    AND `a`.`status` = 1
ORDER BY 
`a`.`priority` DESC
LIMIT 1000
) `t1`
GROUP BY `t1`.`product_code`
ORDER BY 
`t1`.`priority` DESC