如何在MySQL查询中使用DISTINCT而不在输出中显示该字段？

I have this MySQL query:

我有这个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;

Here is the output from the above MySQL query:

以下是上述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 |
+--------------------+-------------------------------+-------------------------+

There is a field called company_name and I want to do a DISTINCT on it:

有一个名为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;

I want to it to be DISTINCT on the field name of 'company_name', but I don't want the company_name field to be displayed in the output. Is there a way to do a DISTINCT company_name without it being displayed? What is the syntax for it and where would it go on the above MySQL query? Or is there another way to do this? Thanks!

我希望它在'company_name'的字段名称上是DISTINCT,但我不希望在输出中显示company_name字段。有没有办法在没有显示的情况下执行DISTINCT company_name?它的语法是什么?它将在上面的MySQL查询中发生什么?或者还有另一种方法吗?谢谢!

1 个解决方案

#1

Add it to the GROUP BY clause and remove it from the SELECT clause:

将其添加到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;

Update: If you want to select the sales as well as the AVG(sales) in the same SELECT clause, you can't do this and you shouldn't. MySQL allows you to do so, but you will get an arbitrary value for sales for each group of Region, company_name.

更新:如果要在同一SELECT子句中选择销售以及AVG(销售),则不能这样做,也不应该这样做。 MySQL允许您这样做,但是您将获得Region,company_name的每个组的销售的任意值。

This is called the single value rule when using a GROUP BY and it is standard to follow this. This means, every column in the SELECT clause, must be in either an a GROUP BY or an aggregate functions. You can't select a row that is not in a GROUP BY nor in an aggregate function. In your case you already select the sales with an aggregate function AVG, why you want to select sales also in the same query?

这在使用GROUP BY时称为单值规则,并且遵循此标准是标准的。这意味着,SELECT子句中的每一列都必须位于GROUP BY或聚合函数中。您不能选择不在GROUP BY中或在聚合函数中的行。在您的情况下,您已经选择了具有聚合函数AVG的销售,为什么您还要在同一查询中选择销售?

However, If you want to do this anyway, you can do this:

但是,如果你想要这样做,你可以这样做:

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;

Assuming that Region, company_name are coming form the dist table.

假设Region,company_name来自dist表。

Note that: I used INNER JOIN instead of the implicit join syntax in your query, they are the same.

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

But I am not sure for the consistent of the sales values for each group of Region, Company_name.

但我不确定每组Region,Company_name的销售价值是否一致。

#1