mysql - SQL GROUP BY来自两列的计算值

Question

我有一个具有价格和折扣列的产品表。我想按价格将产品分组（因此，当2个产品的价格相同时，它只会返回一个）

这个作品：

select * from `products` group by `price` order by `price` asc

但是我的问题是，是否可以按计算出的最终价格对产品进行分组，例如（价格-价格*折扣/ 100作为price_after_discount）

这个：

select *, (price - (price * discount / 100)) AS price_after_discount from `products` order by `price_after_discount` asc

返回值：

[
  0 => [
    "id" => 2284
    "price" => 10.0
    "discount" => 5
    "price_after_discount" => 9.5
  ]
  1 => [
    "id" => 2281
    "price" => 10.0
    "discount" => 0
    "price_after_discount" => 10.0
  ]
  2 => [
    "id" => 2286
    "price" => 10.0
    "discount" => 0
    "price_after_discount" => 10.0
  ]
]

如您所见，共有3种型号，其中2种具有相同的最终价格。

但是，当我添加group by语句时：

select *, (price - (price * discount / 100)) AS price_after_discount from `products` group by `price_after_discount` order by `price_after_discount` asc

它仅返回以下内容：

[
  0 => [
    "id" => 2281
    "price" => 10.0
    "discount" => 0
    "price_after_discount" => 10.0
  ]
]

请如何修改查询，以便它将按此顺序返回ID为2284和2281（或2286）的2个模型？

（这只是生成Laravel的SQL命令的转录，因此熟悉Laravel，雄辩或DB Facade解决方案的任何人也将不胜感激:-)）

谢谢

Answer 1

你说：

group by `price_after_discount` order by `price_after_discount`

您需要知道GROUP BY在计算结果集，对其进行别名化之前（或期间）进行操作，但是ORDER BY在创建结果集并完成列别名化之后进行操作。这意味着在数据库执行对数据分组的投标时， price_after_discount不能用作别名。因此，您必须重新陈述计算组中 price_after_discount的公式，方法是：

select *, (price - (price * discount / 100)) AS price_after_discount 
from `products` 
group by (price - (price * discount / 100))
order by `price_after_discount`

从本质上看起来好像您在运行两次计算，不要被推迟；大多数数据库引擎都足够聪明，可以看到两个构造完全相同，它们不会运行两次

您也可以使用一些查询嵌套来鼓励数据库引擎首先对别名结果集进行一些准备*：

select *
from
  (
    select *, (price - (price * discount / 100)) AS price_after_discount 
    from `products` 
  ) as subquery1
group by `price_after_discount`
order by `price_after_discount`

在此，price_after_discount在数据传递到外部查询之前成为子查询的一部分，因此在分组时可用

如果您仔细看一下数据库的功能，了解这最后一部分可能是一个谎言，这可能也会有所帮助。更好的RDBMS会将您的查询分开，并寻找优化查询的方法。如果您要向各种数据库索取有关它们在此处的两个查询的报告，我毫不怀疑其中某些数据库会产生两个相同的计划，因为优化器很聪明，可以意识到它们是有效地相同。如果您确实感兴趣，请只听我建议的最后一部分，否则，如果您的特定数据库没有其他压倒性的性能问题，请选择您认为更美观，更易于理解的格式

进一步警告。.我假设这是伪代码，或者通过为所有未分组的列插入特定的聚合来使您的数据库“有用”：

在我日常工作的典型数据库（Oracle，MSSQL）中，您不能说：

SELECT name, age, social_security_number FROM people GROUP BY name

因为数据库想知道您要对所有您未分组的东西做什么。假设表中有两个John，一个为99，另一个为14.您希望DB返回哪个年龄段？ 99还是14？您以为数据库对将记录数据保持在一起有既得利益，但是使用GROUP BY时，所有这些都不在窗外

通常，我们使用 MAX(age)， MIN(Age)， SUM(age)等指定对未分组数据的处理方式。

我们必须对数据库说我们希望它与与通用名称关联的所有多个值一起做什么。如果您的数据库允许您不说要应用哪种聚合，则可能默认选择一些聚合。根本不可能，因为GROUP BY要求将多个具有重复标识符的记录压缩为一个；如果您还要求数据库将数据返回给您，则根本无法不指定如何处理其他数据。

您说过“（因此，当两种产品的价格相同时，它只会返回一种）”-您必须告诉它哪种。在名称示例中，它很容易理解，因为它是人为设计的，不计算任何内容，这将是这样的：

SELECT people.* FROM 
  people
  INNER JOIN
  (SELECT name, max(age) as maxage FROM people GROUP BY name) as finder
  ON people.name = finder.name AND people.age = finder.age

有一个子查询，提供了唯一名称的列表以及该名称的最长使用期限。通过将其重新连接到人员数据表，我们可以将从人员中获得的行限制为姓名和最大年龄配对匹配的人员。本质上，此查询是“查找具有特定名称的最老的人”。

如果有两个99岁的约翰，会发生什么？您将同时获得它们。.因此，您需要一个比“我想要每个命名的人中年龄最大的人”更好的选择器。.必须引入一些打破常规的方法，并将其添加到查询中

对于折价后的价格，如果MyQL只是通过为您提供未准确指定的MIN（）来“帮助”您，那么您可能会得到以下结果：

[
  0 => [
    "id" => 2281
    "price" => 10.0
    "discount" => 0
    "price_after_discount" => 10.0
  ]
]

如果您的数据是这样的：

[
  0 => [
    "id" => 2284
    "price" => 10.0
    "discount" => 5
    "price_after_discount" => 9.5
  ]
  1 => [
    "id" => 2281
    "price" => 20.0
    "discount" => 50
    "price_after_discount" => 10.0
  ]
  2 => [
    "id" => 2287
    "price" => 10.0
    "discount" => 0
    "price_after_discount" => 10.0
  ]
]

通过对10.0折后的价格进行分组，但只是让MySQL通过“选择”其他值来“帮助”它，它的选择是为您提供每个值的MIN（），这使记录混合起来。给定2281（是） ，在2281和2287中，2281是较低的价格，价格为10（是，在20和10中，10是较低的价格），折扣为0（是，在50和0中，0是较低的价格） ），但现在一切都乱七八糟：没有充分的理由说明，如果数据库在没有显式指令的情况下随机决定要做什么，那么它甚至会将行数据保持在一起。

但是，它已经正确地遵守了自己的规则，将所有内容归为“所有10.0的折扣价”，然后决定为您提供每一个可以找到的最小数量。

您必须决定所需的WHICH产品，并运行查询以根据该条件找到它们并返回它们：

SELECT * FROM 
products
INNER JOIN
(SELECT MAX(id) as id FROM products GROUP BY (price - (price * discount/100)) finder
ON finder.id = products.id

希望您现在知道为什么这样工作：您要求mysql为您准备一个特定折扣价的所有最高ID号的列表。您实际上并不在乎（顺便说一句）传回折扣价，您只需要进行计算，找到组中最高的ID，然后将其加入产品表以获取其余详细信息对于该max（）ed ID