表如下
id time val
200501 2009-6-2 18
200520 2009-6-2 30
200521 2009-6-2 270
XXXX想要得到结果是2005 2009-6-2 30 270就是找到所有ID结尾是20的数据,查找同表中 头4位一样,并且时间相同,并且以‘21’结尾的记录 取出他的VAL 放到 20结尾记录的最后一个列
请各位不吝赐教3Q 急
id time val
200501 2009-6-2 18
200520 2009-6-2 30
200521 2009-6-2 270
XXXX想要得到结果是2005 2009-6-2 30 270就是找到所有ID结尾是20的数据,查找同表中 头4位一样,并且时间相同,并且以‘21’结尾的记录 取出他的VAL 放到 20结尾记录的最后一个列
请各位不吝赐教3Q 急
select * from tab where id='XXXX20'+1
我写出来的 错误SQL是
select RTRIM(a.SENID) as SENID, a.TIME, a.FACTV, b.FACTV
from "THFB"."RTSQ" a left outer join (select SENID, TIME, FACTV from "THFB"."RTSQ") b
on RTRIM(a.senid) = SUBSTR(RTRIM(b.SENID),0,4) || '21' and a.time = b.time 不知道是否可以修改下酒可以使用?结果有点接近可又不正确
insert into t_10 values('200501',to_date('2009-6-2','yyyy-mm-dd'),18);
insert into t_10 values('200520',to_date('2009-6-2','yyyy-mm-dd'),30);
insert into t_10 values('200521',to_date('2009-6-2','yyyy-mm-dd'),270);select a.id,a.val,b.val
from
(select * from t_10 where substr(id,-2,2)='20') a,
(select * from t_10 where substr(id,-2,2)='21') b
where a.time=b.time
select a.id,a.time,a.val,b.val
from tab a,tab b
where substr(a.id,-2)='20' and b.id=substr(a.id,1,4)||'21';
select a.id,a.time,a.val,b.val
from tab a,tab b
where substr(a.id,-2)='20' and b.id=substr(a.id,1,4)||'21' and a.time=b.time;
就是 20有的时候 我希望 21找不到时显示NULL
而21有时候 如果没有20就不显示 类似LEFT JOIN的功能
当前的SQL 似乎只有2个都存在时才显示
这种需求应该怎么实现?
select val from tab
where id=
(select substr(id,-2,2) new_id from tab where new_id='21')
)
where id=(
select substr(id,-2,2) old_id from tbl where old_id='20'
)
update tab set val=(
select val from tab
where id=
(select substr(id,-2,2) new_id from tab where new_id='21')
)
where id=(
select substr(id,-2,2) old_id from tab where old_id='20'
)