sql - 如何只选择表中出现次数最少的记录？

Question

简化我的数据库示例是一个表

        CN REG#         CAPACITY
---------- ---------- ----------
         0 LUCY02          43000
         6 QRT834           5550
         3 SYF777           3333
        12 PKR768           1234
         5 SST005          12000
         6 KKK007          10000
         3 PKR008          22000

这是通过连接两个表创建的。这是连接语句:

select count(tp.reg#) as cn,t.reg#,  CAPACITY
from truck t
join trip tp 
on  t.reg# = tp.reg#
group by tp.reg#,  CAPACITY, t.reg#

现在，我怎样才能编写一个语句来仅打印出现次数最少且非零的行。在我们的示例中为 3。

预期输出为

     3 SYF777           3333
     3 PKR008          22000

到目前为止我所做但不起作用的是:

select count(tp.reg#) as cn,t.reg#,  CAPACITY
from truck t
left join trip tp 
on  t.reg# = tp.reg#
left join
(                       
    select dd, min(d) as ds
    from(
        select count(*)as d, ti.CAPACITY, ti.reg# as dd
        from truck ti, trip tp 
        where tp.reg# = ti.reg#
        group by ti.reg#, ti.CAPACITY
    )
    group by dd
)
on t.reg# = dd
group by tp.reg#,  CAPACITY, t.reg#

Answer 1

如果您想根据“count”等聚合函数过滤查询，只需在“having”语句中添加此过滤器即可:

select count(tp.reg#) as cn,t.reg#,  CAPACITY
from truck t
join trip tp 
on  t.reg# = tp.reg#
group by tp.reg#,  CAPACITY, t.reg#
having count(tp.reg#) >= 3

如果想动态查询出现次数最少的记录，可以使用下面的代码:

select count(tp.reg#) as cn,t.reg#,  CAPACITY
from truck t
join trip tp 
on  t.reg# = tp.reg#
group by tp.reg#,  CAPACITY, t.reg#
having count(tp.reg#) = (select distinct top 1 count(tp.reg#) cn
                         from truck t
                         join trip tp 
                         on  t.reg# = tp.reg#
                         group by tp.reg#,  CAPACITY, t.reg#
                         having count(tp.reg#) >0
                         order by cn)