--1 你第一个是可以的--2 用row_number() over(partition by ) select col,col2,.... from (select col,col2,....,row_number() over(partition by col1 order by col2) rn from tb) a where rn=1-或者用聚合函数吧
顶!select col1,col2,col3 from tab group by col1,col2,col3;--要显示哪些字段,就对显示的字段进行分组
第二题: 我觉得可以用group by分组 然后having吧。 有无主键无关,都可以通过....group by col1,col2...having count(0)=1 group by列中无主键列。 比如: with t as( select 1 cl1,2 cl2 from dual union all select 2,3 from dual union all select 3,6 from dual union all select 1,2 from dual union all select 2 ,3 from dual union all select 4,5 from dual )select cl1,cl2,count(1) as countnum from t group by cl1,cl2;
例子丢了一句,应该为: with t as( select 1 cl1,2 cl2 from dual union all select 2,3 from dual union all select 3,6 from dual union all select 1,2 from dual union all select 2 ,3 from dual union all select 4,5 from dual )select cl1,cl2 from t group by cl1,cl2 having count(1)=1;
如果列太多的话,性能会很低啊 row_number() over (partition by ) 更好一点
--1
你第一个是可以的--2 用row_number() over(partition by )
select col,col2,....
from
(select col,col2,....,row_number() over(partition by col1 order by col2) rn
from tb) a
where rn=1-或者用聚合函数吧
from tab
group by col1,col2,col3;--要显示哪些字段,就对显示的字段进行分组
我觉得可以用group by分组 然后having吧。
有无主键无关,都可以通过....group by col1,col2...having count(0)=1
group by列中无主键列。
比如:
with t as(
select 1 cl1,2 cl2 from dual
union all
select 2,3 from dual
union all
select 3,6 from dual
union all
select 1,2 from dual
union all
select 2 ,3 from dual
union all
select 4,5 from dual
)select cl1,cl2,count(1) as countnum
from t
group by cl1,cl2;
with t as(
select 1 cl1,2 cl2 from dual
union all
select 2,3 from dual
union all
select 3,6 from dual
union all
select 1,2 from dual
union all
select 2 ,3 from dual
union all
select 4,5 from dual
)select cl1,cl2
from t
group by cl1,cl2
having count(1)=1;
row_number() over (partition by )
更好一点