表:A_TB
字段: ZD1, ZD2, ZD3, ZD4, ZD5
值: 01 xx b gg A
01 ff d ss B
...
02 dd f vv C
我要按照ZD5进行分组,然后分别取100条记录,按照上面的值来说就是 去ZD5为A的100条,ZD5为B的100条,ZD5为C的100条依次类推,对于ZD1也有要求,要ZD1=01的;
单独取的语句我会写:
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='A' and rownum<=100;
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='B' and rownum<=100;
....等等请问如何用一条语句取出以上多条sql语句才能取得的数据,我想应该用group by 和having 子句,请大家帮忙
字段: ZD1, ZD2, ZD3, ZD4, ZD5
值: 01 xx b gg A
01 ff d ss B
...
02 dd f vv C
我要按照ZD5进行分组,然后分别取100条记录,按照上面的值来说就是 去ZD5为A的100条,ZD5为B的100条,ZD5为C的100条依次类推,对于ZD1也有要求,要ZD1=01的;
单独取的语句我会写:
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='A' and rownum<=100;
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='B' and rownum<=100;
....等等请问如何用一条语句取出以上多条sql语句才能取得的数据,我想应该用group by 和having 子句,请大家帮忙
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='A' and rownum<=100
union all
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='B' and rownum<=100
union all
select ZD1,ZD2,ZD5 from A_TB where ZD1='01' and ZD5='C' and rownum<=100...
select ZD1,ZD2,ZD5,row_number() over(partition by ZD5 order by rownum)rn from A_TB where ZD1='01'
select ) where rn <=100