Oracle -确定行数并打印行数

Question

我正在尝试确定select语句返回的行是否超过1行。如果这是假的，我不想要任何结果。我已经尝试了以下几种方法。

select s.id_numeric,s.client_id,s.depth,s.fas_sample_type,s.profile_number
from sample s 
where s.client_id = upper ('128336A') 
and s.id_numeric between 12325 and 12327
and s.fas_sample_type = sample_pkg.get_soil_sample
and s.status = sample_pkg.get_authorised_sample
and s.flg_released = constant_pkg.get_true
and rownum >= 1

上面的查询之所以有效，是因为我添加了rownum >= 1。但是一旦我添加了rownum >1，就没有结果了。

Answer 1

这应该会有帮助：

with base_data as (
  select s.id_numeric,
         s.client_id,
         s.depth,
         s.fas_sample_type,
         s.profile_number
  from   sample s 
  where  s.client_id = upper ('128336A') 
  and    s.id_numeric between 12325 and 12327
  and    s.fas_sample_type = sample_pkg.get_soil_sample
  and    s.status = sample_pkg.get_authorised_sample
  and    s.flg_released = constant_pkg.get_true
)
select *
from   base_data b
where  (select count(*) from base_data) > 1

Answer 2

尝试使用analytical function，如下所示：

Select * from
(select s.id_numeric,
         s.client_id,
         s.depth,
         s.fas_sample_type,
         s.profile_number,
         Count(1) over() as cnt
  from   sample s 
  where  s.client_id = upper ('128336A') 
  and    s.id_numeric between 12325 and 12327
  and    s.fas_sample_type = sample_pkg.get_soil_sample
  and    s.status = sample_pkg.get_authorised_sample
  and    s.flg_released = constant_pkg.get_true)
Where cnt > 1

干杯！！