表a
id data date
1 32 0102
1 23 0203
1 12 0403
2 33 0121
2 42 0311
... ... ....目的结果
以date=0300为界统计出结果。例如
id data flag
1 55 1
1 12 2
2 33 1
2 42 2
... ... ...
怎么能用一条sql查出来
id data date
1 32 0102
1 23 0203
1 12 0403
2 33 0121
2 42 0311
... ... ....目的结果
以date=0300为界统计出结果。例如
id data flag
1 55 1
1 12 2
2 33 1
2 42 2
... ... ...
怎么能用一条sql查出来
SQL>
SQL> with a as (
2 select 1 id, 32 data, '0102' datte from dual union all
3 select 1 id, 23 data, '0203' datte from dual union all
4 select 1 id, 12 data, '0403' datte from dual union all
5 select 2 id, 33 data, '0121' datte from dual union all
6 select 2 id, 42 data, '0311' datte from dual
7 )
8 select id,data,case when datte<='0300' then 1 else 2 end from a
9 ; ID DATA CASEWHENDATTE<='0300'THEN1ELSE
---------- ---------- ------------------------------
1 32 1
1 23 1
1 12 2
2 33 1
2 42 2SQL>
2 select 1 id, 32 data, '0102' datte from dual union all
3 select 1 id, 23 data, '0203' datte from dual union all
4 select 1 id, 12 data, '0403' datte from dual union all
5 select 2 id, 33 data, '0121' datte from dual union all
6 select 2 id, 42 data, '0311' datte from dual
7 )
8 select id, sum(data), flag
9 from (select id,
10 data,
11 case
12 when datte <= '0300' then
13 1
14 else
15 2
16 end flag
17 from a)
18 group by id, flag
19 ; ID SUM(DATA) FLAG
---------- ---------- ----------
2 33 1
1 12 2
1 55 1
2 42 2SQ
select 1 id, 32 data, '0102' datte from dual union all
select 1 id, 23 data, '0203' datte from dual union all
select 1 id, 12 data, '0403' datte from dual union all
select 2 id, 33 data, '0121' datte from dual union all
select 2 id, 42 data, '0311' datte from dual
)
select id, sum(data), flag
from (select id,
data,
case
when datte <= '0300' then
1
else
2
end flag
from a)
group by id, flag order by id,flag
我想楼主的flag 1,2...是这样递增的吧 而不只有两个吧?
我的意思是说,这里1,2 应该递增来求得,而不是用case when
我也不知道楼主到底什么需求
呵呵不和你扯了。。go back!!!