有这么一张表
CREATE TABLE table_test
(
ID NUMBER,
NAME VARCHAR2(80),
age NUMBER,
birthplace varchar2(240)
)
插入数据
INSERT INTO table_test VALUES(1,'TOM',20,'China');INSERT INTO table_test VALUES(2,'TOM',23,'China');想实现一条语句就是当NAME、birthplace相同时,下一条数据的age为0,实现效果ID NAME AGE BIRTHPLACE
1 TOM 20 CHINA
2 TOM 0 CHINA请问用一条sql语句如何实现呢?
CREATE TABLE table_test
(
ID NUMBER,
NAME VARCHAR2(80),
age NUMBER,
birthplace varchar2(240)
)
插入数据
INSERT INTO table_test VALUES(1,'TOM',20,'China');INSERT INTO table_test VALUES(2,'TOM',23,'China');想实现一条语句就是当NAME、birthplace相同时,下一条数据的age为0,实现效果ID NAME AGE BIRTHPLACE
1 TOM 20 CHINA
2 TOM 0 CHINA请问用一条sql语句如何实现呢?
SELECT t.*,
lag(t.name || t.birthplace) over(PARTITION BY t.name, t.birthplace ORDER BY id) lab
FROM table_test t);
m.NAME,
DECODE(m.cnt, 1, m.age, 0) age,
m.birthplace
FROM (SELECT t.*,
row_number() OVER(PARTITION BY t.NAME, t.birthplace ORDER BY t.ID) cnt
FROM table_test t) m
ORDER BY 1
from table_test a
left join (select min(id) id,min(age) age,name,birthplace,min(name||birthplace) lab
from table_test
group by name,birthplace) b
on a.id = b.id
order by a.id老的方法