excel中用公式来筛选重复数据可以用countif函数,假定数据在a1-a12单元格,在b1输入公式=IF(COUNTIF(A$1:A$12,A1&"*")>1,COUNTIF(A$1:A$12,A1&"*")&"个重复数据","没有重复数据"),向下拉复制公式即可得出结果,如下图所示。
建两个序列,并通过一个函数调用序列(union 不支持直接使用序列),用于排序。
先用第二组所有行(3行),union all 3遍第一组第一行(left join 第二组,行数就和第二组一样了)
在用第二组所有行(3行),union all 3遍第一组第二行(left join 第二组,行数就和第二组一样了)
最后按照序号,组别,编号排序
/*drop sequence Big_Letter1
Create sequence Big_Letter1
Increment by 1
Start with 65
Maxvalue 999999
Minvalue 1
Nocycle
nocache
drop sequence Big_Letter2
Create sequence Big_Letter2
Increment by 1
Start with 65
Maxvalue 999999
Minvalue 1
Nocycle
nocache
--获取数列下一个值
create or replace function get_seq_next (seq_name in varchar2) return number
is
seq_val number
begin
execute immediate 'select '|| seq_name|| '.nextval from dual' into seq_val
return seq_val
end get_seq_next
*/
with tmp as (
select '1' groupid,'1' num from dual
union
select '1','2' from dual
union
select '2','1' from dual
union
select '2','2' from dual
union
select '2','3' from dual
)
select chr(get_seq_next('Big_Letter1')) xuhao,t1.groupid ,t1.num
from tmp t1 where groupid='2'
union all
select chr(get_seq_next('Big_Letter2')) xuhao,t1.groupid ,t1.num
from tmp t1,tmp t2
where t1.groupid='1' and t1.num='1' and t2.groupid='2'
union
select chr(get_seq_next('Big_Letter1')) xuhao,t1.groupid ,t1.num
from tmp t1 where groupid='2'
union all
select chr(get_seq_next('Big_Letter2')) xuhao,t1.groupid ,t1.num
from tmp t1,tmp t2
where t1.groupid='1' and t1.num='2' and t2.groupid='2'
order by xuhao,groupid,num
执行结果如下:
按照这个思路拼动态SQL吧
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)