表ta:
lotid trxtype
1 a
1 b
1 b
1 c
1 c
1 c
2 a
2 b
2 b
2 c
2 c
想要得到的结果:
lotid trxtype
1 c
2 b
或者
lotid trxtype
1 c
2 c
即要得到每个lotid对应的记录最多的一个trxtype
请问这样的语句应该怎么写?
lotid trxtype
1 a
1 b
1 b
1 c
1 c
1 c
2 a
2 b
2 b
2 c
2 c
想要得到的结果:
lotid trxtype
1 c
2 b
或者
lotid trxtype
1 c
2 c
即要得到每个lotid对应的记录最多的一个trxtype
请问这样的语句应该怎么写?
insert into t2
select 1,'a' from dual union all
select 1,'b' from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 1,'c' from dual union all
select 1,'c' from dual union all
select 2,'a' from dual union all
select 2,'b' from dual union all
select 2,'b' from dual union all
select 2,'c' from dual union all
select 2,'c' from dual;
--执行查询
select lotid,trxtype from
(
select lotid,trxtype, row_number() over (partition by lotid
order by lotid,c desc) rn
from (select lotid,trxtype,count(*) c from t2 group by lotid,trxtype)
) where rn=1
--查询结果
1 c
2 b