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

最新推荐文章于 2025-04-09 17:03:36 发布

转载最新推荐文章于 2025-04-09 17:03:36 发布 · 421 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jiangxiaobo/p/11008373.html

本文详细解析了在SQL查询中使用子查询、聚合函数、GROUP BY及ORDER BY子句的高级技巧，特别是在复杂的数据筛选和排序场景下如何优化查询效率。通过具体案例，展示了如何结合LIMIT关键字进行结果限制，以及如何确保ORDER BY子句中的列遵循正确的语法规范。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

转载于:https://www.cnblogs.com/jiangxiaobo/p/11008373.html