sql.获得最大值(平均(列)) [英] sql. Getting a max(avg(column))

问题描述

我已经简化了我的问题并创建了这个例子来更好地说明.

I've simplified my problem and created this example to better illustrate.

我的桌子:

CREATE TABLE `table_company` (
  `source_id` INT(10) UNSIGNED NOT NULL,
  `company_id` INT(10) UNSIGNED NOT NULL,
  `clicks` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`source_id`,`company_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

我的数据:

INSERT INTO table_company VALUES
(1,100,8),(2,100,7),(3,200,9),(4,300,3),(5,100,4),(6,400,5),(7,100,10),(8,500,4),(9,500,9),(10,400,9);


SELECT source_id,company_id,AVG(clicks) AS avg_click FROM table_company
GROUP BY source_id,company_id
ORDER BY avg_click DESC;

给我:

source_id  company_id  avg_click  
---------  ----------  -----------
        7         100      10.0000
        3         200       9.0000
        9         500       9.0000
       10         400       9.0000
        1         100       8.0000
        2         100       7.0000
        6         400       5.0000
        5         100       4.0000
        8         500       4.0000
        4         300       3.0000

我想写一个查询给我:

source_id  company_id  avg_click  
---------  ----------  -----------
        7         100      10.0000
        3         200       9.0000
        9         500       9.0000
       10         400       9.0000
        4         300       3.0000

即.只保留具有 MAX(avg_click) 的 company_id

ie. Only keep the company_id that have the MAX(avg_click)

推荐答案

SELECT source_id, company_id, MAX(avg_click) as max_click FROM 
(SELECT source_id,company_id,AVG(clicks) AS avg_click FROM table_company
GROUP BY source_id,company_id
ORDER BY avg_click DESC) tmp
GROUP BY company_id

这篇关于sql.获得最大值(平均(列))的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！