Row Values Group
1 2000 1
2 3000 1
3 1000 1
4 5000 1
5 2000 2
6 2100 2
7 3200 3
8 3300 3
9 3400 3
结果
Values Group
2000 1
3000 1
6000 1
2000 2
2100 2
3200 3
3300 3
3400 3
我要实现的效果是对组进行指定显示n行,第n行及以后的数据全部合计,不足n行的显示原记录。
请问如何一条SQL实现?
1 2000 1
2 3000 1
3 1000 1
4 5000 1
5 2000 2
6 2100 2
7 3200 3
8 3300 3
9 3400 3
结果
Values Group
2000 1
3000 1
6000 1
2000 2
2100 2
3200 3
3300 3
3400 3
我要实现的效果是对组进行指定显示n行,第n行及以后的数据全部合计,不足n行的显示原记录。
请问如何一条SQL实现?
union all
select Values , Group from
(
select t.* , row_number() over(partition by group order by row) px from tb
) m where px <= 3 and group not in (select group from tb group by group having count(1) <= n)
union all
select sum(Values) valuues , Group from
(
select t.* , row_number() over(partition by group order by row) px from tb
) m where px > 3 and group not in (select group from tb group by group having count(1) <= n)
group by Group
where
group by
having count(*)>=n
union all
select * from tableName
where
group by
having count(*)<n
自己加上条件
insert into tb values(1 , 2000 , 1)
insert into tb values(2 , 3000 , 1)
insert into tb values(3 , 1000 , 1)
insert into tb values(4 , 5000 , 1)
insert into tb values(5 , 2000 , 2)
insert into tb values(6 , 2100 , 2)
insert into tb values(7 , 3200 , 3)
insert into tb values(8 , 3300 , 3)
insert into tb values(9 , 3400 , 3)select V , G from tb where G in (select G from tb group by G having count(1) <= 3)
union all
select V , G from
(
select t.* , row_number() over(partition by G order by id) px from tb t
) m where px < 3 and G not in (select G from tb where G in (select G from tb group by G having count(1) <= 3))
union all
select sum(V) V, G from
(
select t.* , row_number() over(partition by G order by id) px from tb t
) m where px >= 3 and G not in (select G from tb where G in (select G from tb group by G having count(1) <= 3))
group by G
order by G drop table tb
/*
V G
---------- ----------
2000 1
3000 1
6000 1
2000 2
2100 2
3200 3
3300 3
3400 38 rows selected.
*/
create table tb(id int, V int, G int)
insert into tb values(1 , 2000 , 1)
insert into tb values(2 , 3000 , 1)
insert into tb values(3 , 1000 , 1)
insert into tb values(4 , 5000 , 1)
insert into tb values(5 , 2000 , 2)
insert into tb values(6 , 2100 , 2)
insert into tb values(7 , 3200 , 3)
insert into tb values(8 , 3300 , 3)
insert into tb values(9 , 3400 , 3)SELECT SUM(a.v) v,a.g FROM (
select t.*,row_number() over(PARTITION BY g ORDER BY ID) rn from tb t) a
GROUP BY decode(sign(rn-N),1,0,rn),a.g
ORDER BY a.g
借用3楼的表....指定显示N行我是按照row来排序的...即最小的N行row显示..其它合计...
把上的N改成2就是LZ的结果了...