时间戳

首页 > 解决方案 > MYSQL 在 Group By 中使用限制

mysql - MYSQL 在 Group By 中使用限制

问题描述

我有这种查询需要在每个“区域”组中限制为 3，但它没有按我预期的那样运行。'row_number' 似乎没有相应地排列。一定有一些我错过了或者我不知道的语法。如果有人可以帮我转储 sql Here。MYSQL 5.0 版

我的查询：

set @type = '';
set @num = 0;

SELECT locinvaisle.Area as ar,locinvaisle.Region as rg,custlist.CustomerName as cn,custlist.Custtype ct,
       SUM(data2.quantity/1000) as mtcur,
       @num := if(@type = locinvaisle.Region, @num + 1, 1) as dummy_1,
  @type := locinvaisle.Region as dummy_2,
  @num as row_number

FROM data2

  INNER JOIN custlist ON data2.customeracc = custlist.Customeraccount
  INNER JOIN locinvaisle ON data2.location = locinvaisle.Location

WHERE
   date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01') AND date1 <= DATE_FORMAT('2018-06-11', '%Y-%m-31')
  AND
   data2.unit = 'KG'
  AND
   data2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')
  AND
   locinvaisle.Area = 'peninsular'
  AND
   custlist.Custtype = 'others'

GROUP BY locinvaisle.Region,custlist.CustomerName
HAVING row_number < 3
ORDER BY locinvaisle.Region,mtcur desc

结果 ：

结果

期望的结果（来自虚拟数据库）：

假

标签： mysqlsql

解决方案

在应用分组依据之前，您必须将限制作为完整的子查询（“派生表”）。这些方面的东西：

SELECT 
       ar, rg, cn, ct, sum(quantity)
FROM (
    SELECT
          @row_num :=IF(@prev_value = locinvaisle.Region, @row_num + 1, 1)AS RowNumber
        , locinvaisle.Area as ar
        , locinvaisle.Region as rg
        , custlist.CustomerName as cn
        , custlist.Custtype ct
        , data2.quantity
        , @prev_value := locinvaisle.Region as dummy_2,
    FROM data2
      INNER JOIN custlist ON data2.customeracc = custlist.Customeraccount
      INNER JOIN locinvaisle ON data2.location = locinvaisle.Location
      CROSS JOIN (SELECT @row_num :=1,  @prev_value :='') vars
    WHERE date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01') AND date1 <= DATE_FORMAT('2018-06-11', '%Y-%m-31')
      AND data2.unit = 'KG'
      AND data2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')
      AND  locinvaisle.Area = 'peninsular'
      AND custlist.Custtype = 'others'
    ORDER BY locinvaisle.Region,mtcur desc
    ) d
WHERE rowNumber <= 3
GROUP BY ar, rg, cn, ct
ORDER BY ar, rg, cn, ct

注意：您需要ORDER BY在子查询中应用一个以促进行号计算，但尽管如此，除非您应用最终的 order by 子句，否则不能保证最终输出将按照所需的顺序。

另请注意，在 MySQL 的未来版本（V8 及更高版本）中，应该有一个row_number()函数和over()子句可以用来代替上面看到的变量。

推荐阅读