SELECT COUNT的MAX(SELECT MAX of COUNT)

我有一个表“井”。 它包含列app_rate_unit（型号：为nvarchar）。 我的目标是计算表中的每个不同的值，并让DBMS（MS Server 2005中）给我最多出现一个。

这是我的代码：

SELECT MAX(app_rate_unit) AS MAX_APP
  FROM (SELECT app_rate_unit, COUNT(*) AS co
          FROM dbo.well AS w
         GROUP BY app_rate_unit
        ) AS derivedtbl_1

与它poblem然而，我的DBMS的实际投放量最低计数我。

SideQuestion：如何筛选的外键（在表格中）和NOT NULL（在app_rate_unit）算起？

Answer 1:

select top 1 app_rate_unit, count(*) from dbo.well
group by app_rate_unit
order by count(*) desc

Answer 2:

试试这个

    SELECT 
        COUNT(app_rate_unit)AS MAX_APP , 
        app_rate_unit 
    FROM 
        dbo.well
    WHERE
            app_rate_unit IS NOT NULL
    GROUP BY 
        app_rate_unit 
    ORDER BY 
            MAX_APP DESC

上述脚本会给你计数和项目。 如果你不知道只有一个项目都会有出现的最大数量您可以更改计数。

Answer 3:

select top 1 count(*) as co from dbo.well as w group by app_rate_unit
order by count(*) desc

Answer 4:

在PostgreSQL，我们可以写一个使用最大计数的作为查询

select max(count) from (

select count(id) from Table _name group by created_by,status_id having status_id = 6 ) as Alias

例如

select max(count) from (

select count(id) from orders group by created_by,status_id having status_id = 6 ) as foo

文章来源: SELECT MAX of COUNT