限制输出 group-by sql 查询中出现的次数

Question

我有这个查询，

select rep, companyname,count(companyname) as [count], Commission from customers
group by repid,companyname,Commission

它返回让我们说

rep    companyname     count    commision
1       ABC              1        10%
2       XYZ              2        10%
2       XYZ              1        20%
3       JKL              4        10%
3       JKL              1        30%

“欲望输出”是

rep    companyname     count    commision
2       XYZ              2        10%
2       XYZ              1        20%
3       JKL              4        10%
3       JKL              1        30%

“我想要一个输出”，以便我只显示结果中重复两次或多次的公司。如何修改上面的查询。我使查询变得简单（删除 where 子句）。

Answer 1

我将使用子查询来获取像这样的非唯一公司名称。

select rep, companyname,count(companyname) as [count], Commission from customers
where companyname in (
  select c1.companyname from customers c1
  group by c1.companyname having count(*) >= 2
)
group by repid,companyname,Commission

Answer 2

我想这会符合你的要求。我想不出没有某种子查询或 CTE 的方法：

select 
    rep, companyname, [count], commission
from (
    select 
        rep, companyname,count(companyname) as [count], Commission,
        count(1) over (PARTITION by companyname) as [companycount]
    from customers
    group by repid,companyname,Commission
) sub
where companycount > 1

Answer 3

select rep
     , companyname
     , count(*) as [count]                  --- equivalent to count(companyname)
     , Commission
from customers c
where exists
      ( select *
        from customers c2
        where c2.companyname = c.companyname 
          and ( c2.repid <> c.repid
             or c2.Commission <> c.Commission 
              )
          and ( extra-conditions )
      )
  and ( extra-conditions )
group by repid, companyname, Commission

Answer 4

在分组依据后添加 HAVING 子句，例如 HAVING count(companyName) > 1

Answer 5

您正在寻找 HAVING 关键字，它本质上是 GROUP BY 的 WHERE 条件

select rep, companyname,count(companyname) as [count], Commission from customers
group by repid,companyname,Commission
having count(companyname) > 1