如何使用分组依据从表中计算最大值(Count(Columnname))

Question

如何为以下查询查找数据

原始来源问题89

表格结构：产品（制造商、型号、类型）

编辑使用sqlfiddle进行模式

任务：
找出那些在产品表以及那些拥有最少型号的产品。输出：制造商，型号数量

有问题的提示是GROUP BY、HAVING和IN

我尝试了各种方法来找到这个，基本上我想到的是我需要使用Max(COUNT(model))

结果应为

maker   qty 
A        7 --max
C        1 --min

我想出的一个解决方案是

select maker, count(ps.model) as Count from product as ps
group by ps.maker
having count(ps.model) in
(
select max(cnt) from (select count(model) as cnt from product group by maker)t
union
select min(cnt) from (select count(model) as cnt from product group by maker)t
)

还有别的办法吗。

Answer 1

您可以简单地。。。

SELECT *
FROM (
    SELECT TOP 1 maker, COUNT(*) C
    FROM product
    GROUP BY maker
    ORDER BY C DESC
) Q1
UNION
SELECT *
FROM (
    SELECT TOP 1 maker, COUNT(*) C
    FROM product
    GROUP BY maker
    ORDER BY C
) Q2;

或者如果你喜欢CTE。。。

WITH CTE AS (
    SELECT maker, COUNT(*) C
    FROM product
    GROUP BY maker
)
SELECT *
FROM (
    SELECT TOP 1 *
    FROM CTE
    ORDER BY C DESC
) Q1
UNION
SELECT *
FROM (
    SELECT TOP 1 *
    FROM CTE
    ORDER BY C
) Q2;

如本SQL Fiddle中所示。

Answer 2

如果您使用的是SQL Server 2008，您可以通过以下方式简化它：http://www.sqlfiddle.com/#！3/25ba8/7

with counts as
(
  select maker, count(ps.model) as Count from product as ps
  group by ps.maker
)
select cx.*
from counts cx
join
(
  select min(count) as lo, max(count) as hi
  from counts
) as hi_lo 
on cx.count = hi_lo.lo or cx.count = hi_lo.hi;