mysql - 如何在不在输出中显示该字段的情况下在 MySQL 查询中使用 DISTINCT？

Question

我有这个 MySQL 查询:

select Region,
CONCAT('$', FORMAT(AVG(sales), 0)) as 'Average_Sales_by_Region', 
count(*) as '# of Dist in state'
from dist, Regions_US
where dist.state=Regions_US.State
group by Region ORDER BY AVG(sales) DESC;

这是上述 MySQL 查询的输出:

+--------------------+-------------------------------+-------------------------+
| Region             | Average_Sales_by_Region       | # of Dist in state      |
+--------------------+-------------------------------+-------------------------+
| Alaska-Hawaii      | $8,150                        |                       4 |
| Mountain           | $20,216                       |                      74 |
| West North Central | $18,268                       |                      40 |
| South Atlantic     | $16,225                       |                     178 |
| East South Central | $14,967                       |                      30 |
| West South Central | $13,704                       |                     125 |
| East North Central | $12,668                       |                      79 |
| New England        | $11,916                       |                      32 |
| Pacific            | $11,553                       |                     120 |
| Middle Atlantic    | $10,292                       |                     131 |
+--------------------+-------------------------------+-------------------------+

有一个名为 company_name 的字段，我想对其执行 DISTINCT:

select DISTINCT company_name,
Region,CONCAT('$', FORMAT(AVG(sales), 0)) as 'Average_Sales_by_Region', 
count(*) as '# of Dist in state'
from dist, Regions_US
where dist.state=Regions_US.State
group by Region ORDER BY AVG(sales) DESC;

我希望它在“company_name”的字段名称上是 DISTINCT，但我不希望在输出中显示 company_name 字段。有没有办法在不显示的情况下执行 DISTINCT company_name？它的语法是什么，它会在上面的 MySQL 查询中去哪里？还是有另一种方法可以做到这一点？谢谢!

Answer 1

将其添加到 GROUP BY子句并将其从 SELECT 中删除子句:

SELECT
  Region,
  CONCAT('$', FORMAT(avgSales, 0)) as 'Average_Sales_by_Region', 
  TheCount as '# of Dist in state'
FROM
(
   SELECT 
     Region, 
     AVG(Sales) avgSales, 
     COUNT(*) theCount
   from dist, Regions_US
   where dist.state=Regions_US.State
   group by Region,company_name
) AS sub       
ORDER BY avgSales DESC;

---

更新:如果你想选择sales以及 AVG(sales)在同一SELECT子句，你不能这样做，你也不应该这样做。 MySQL 允许您这样做，但是您将获得 sales 的任意值。对于每组 Region, company_name .

这在使用 GROUP BY 时称为单值规则遵循这一点是标准的。这意味着，SELECT 中的每一列子句，必须在 GROUP BY 中或聚合函数。您不能选择不在 GROUP BY 中的行也不在聚合函数中。在您的情况下，您已经选择了 sales使用聚合函数 AVG , 为什么要选择 sales也在同一个查询中？

但是，如果您仍然想这样做，您可以这样做:

SELECT
  Region,
  CONCAT('$', FORMAT(avgSales, 0)) as 'Average_Sales_by_Region', 
  TheCount as '# of Dist in state',
  (SELECT sales
   FROM dist d2
   WHERE d2.Region       = d1.Region
     AND d2.company_name = d1.company_name) AS Sales
FROM
(
   SELECT 
     Region, 
     AVG(Sales) avgSales, 
     COUNT(*) theCount
   from dist
   INNER JOIN Regions_US ON dist.state = Regions_US.State
   group by Region,company_name
) AS sub       
ORDER BY avgSales DESC;

假设Region, company_name来自 dist表。

请注意:我使用了 INNER JOIN与查询中的隐式连接语法不同，它们是相同的。

但我不确定 sales 是否一致每组的值 Region, Company_name .