以下为bill查询结果(select * from bill)
billno amount
A 1 50
A 2 100
A 3 150
A 4 200
A 8 300
A 9 150
A 12 200
B 13 300请想办法查询成以下结果:
start end sum(amount)
A 1 4 500
A 8 9 450
A 12 12 200
B 13 13 300
billno amount
A 1 50
A 2 100
A 3 150
A 4 200
A 8 300
A 9 150
A 12 200
B 13 300请想办法查询成以下结果:
start end sum(amount)
A 1 4 500
A 8 9 450
A 12 12 200
B 13 13 300
billno amount
A 1 50
A 2 100
A 3 150
A 4 200
A 8 300
A 9 150
A 12 200
B 13 300
请想办法查询成以下结果:
start end sum(amount)
A 1 4 500
A 8 9 450
A 12 12 200
B 13 13 300
用ROWNUMBER能写吧,不过有些麻烦。
有没有最好的方法。
按billno是否是一个基数为1的递增,
select t.,t.billno,lead(billno,1) over(partition by order by billno)aa from bill t
billno aa
A 1 2
A 2 3
A 3 4
A 4 8
A 8 9
A 9 12
A 12
B 13只需要考虑r.aa-r.billno!=1 or r.aa is null,等于1的就是递增的 billno aa
A 4 8
A 9 12
A 12
B 13 分段结果应该是 min(billno)--4
8----9
12----12
13 ---13
:(到这想不下去了。
(SELECT MARK, BILLNO,(BILLNO-ROWNUM) C,AMOUNT FROM BILL ORDER BY MARK,BILLNO)
GROUP BY MARK, C
billno - rownum
呵呵,找规律的能力真强啊
WHERE MARK BETWEEN '1' AND '4'GROUP BY MARK UNION ALL
Select MARK,MIN(BILLNO),MAX(BILLNO),SUM(AMOUNT)from (SELECT * FROM bill
WHERE BILLNO BETWEEN '8' AND '9') GROUP BY MARK UNION ALL
Select MARK,MIN(BILLNO),MAX(BILLNO),SUM(AMOUNT)from bill
WHERE BILLNO='12'GROUP BY MARK UNION ALL
Select MARK,MIN(BILLNO),MAX(BILLNO),SUM(AMOUNT)from bill WHERE BILLNO='13'GROUP BY MARK答案二:select ,min(billno),max(billno),sum(amount) from bill where rownum<5 group by
union all
select ,min(billno),max(billno),sum(amount) from
(select * from bill where rownum<7 minus select * from bill where rownum<5) group by
union all
select ,min(billno),max(billno),sum(amount) from
(select * from bill where rownum<8 minus select * from bill where rownum<7) group by
union all
select ,min(billno),max(billno),sum(amount) from
(select * from bill where rownum<9 minus select * from bill where rownum<8) group by 已验证
那么只要group by [billno /4]
from (
select ,nvl(lag(billno)over(partition by order by previos),minbillno) startbill,
nvl(previos,maxbillno) endbill from(
select ,billno,lag(billno,1) over(partition by order by billno) previos
,min(billno)over(partition by ) minbillno,
max(billno) over(partition by ) maxbillno,amount from bill)
where nvl(billno-previos-1,1)<>0)
SELECT MARK,MIN(BILLNO),MAX(BILLNO),SUM(AMOUNT) FROM
(SELECT MARK, BILLNO,(BILLNO-ROWNUM) C,AMOUNT FROM BILL ORDER BY MARK,BILLNO)
GROUP BY MARK, C
--------強...主要是要想到按(BILLNO-ROWNUM)做Group by...
SELECT ,billno,amount, billno-dense_rank() over(partition by order by lpad(billno,3,'0000')) devide_no
FROM bill)
GROUP BY , devide_no;
顺便问一句,以后再遇到类似问题的时候,我们应该怎么去查呢,因为数据库没有方便的API
学习中
FROM (SELECT , billno,
DECODE (TRUNC ((billno) / 4),
0, 1,
TRUNC ((billno) / 4)
) devide_no,
amount
FROM bill)
GROUP BY , devide_no
/
select , min(billno),max(billno),sum(amount) from
(
select ,billno,billno-rank flag,amount from
(
SELECT ,billno,row_number() over(partition by order by billno) rank,amount FROM bill
) a
)gg
group by ,flag
declare @start int
declare @end int
declare @ nvarchar(10)
declare @amount int
declare @index intset @amount=0
select @start=MAX(billno),@end=MAX(billno) from bill
select @start as start,@end as [end],,amount into bill2 from bill where billno=@end
select * from bill2
set @index=@end
while(@index>0)
begin
set @index=@index-1
set @amount=0
if exists(select * from bill where billno=@index)
begin
select @amount=amount,@= from bill where billno=@index
if exists(select * from bill2 where =@ and start=@index+1)
begin
Update bill2 set start=@index,amount=amount+@amount where =@ and start=@index+1
end
else
begin
insert into bill2(start,[end],,amount) values(@index,@index,@,@amount)
end
end endselect * from bill2 order by startDROP TABLE bill2