create table tb( ID int, 时间 datetime, 值 int)
insert into tb values(1 , '2007-3-1' ,10 )
insert into tb values(1 , '2007-3-9' ,12 )
insert into tb values(1 , '2007-3-17' ,21 )
insert into tb values(2 , '2007-3-7' ,14 )
insert into tb values(2 , '2007-3-9' ,16 )
insert into tb values(2 , '2007-3-10' ,11 )
goselect * , val = (select top 1 值 from tb where id = t.id and 时间 < t.时间 order by 时间 desc) from tb tdrop table tb/*ID 时间 值 val
----------- ------------------------------------------------------ ----------- -----------
1 2007-03-01 00:00:00.000 10 NULL
1 2007-03-09 00:00:00.000 12 10
1 2007-03-17 00:00:00.000 21 12
2 2007-03-07 00:00:00.000 14 NULL
2 2007-03-09 00:00:00.000 16 14
2 2007-03-10 00:00:00.000 11 16(所影响的行数为 6 行)
*/
insert into tb values(1 , '2007-3-1' ,10 )
insert into tb values(1 , '2007-3-9' ,12 )
insert into tb values(1 , '2007-3-17' ,21 )
insert into tb values(2 , '2007-3-7' ,14 )
insert into tb values(2 , '2007-3-9' ,16 )
insert into tb values(2 , '2007-3-10' ,11 )
goselect * , val = (select top 1 值 from tb where id = t.id and 时间 < t.时间 order by 时间 desc) from tb tdrop table tb/*ID 时间 值 val
----------- ------------------------------------------------------ ----------- -----------
1 2007-03-01 00:00:00.000 10 NULL
1 2007-03-09 00:00:00.000 12 10
1 2007-03-17 00:00:00.000 21 12
2 2007-03-07 00:00:00.000 14 NULL
2 2007-03-09 00:00:00.000 16 14
2 2007-03-10 00:00:00.000 11 16(所影响的行数为 6 行)
*/
我使用 select * ,(select top 1 值 from tb where id = t.id and 时间 < t.时间 order by 时间 desc) as VAL from tb t
提示我缺失右括号?
我随便打开了个ORALCE表
输入如下SQL:
select "WDS"."RTU"."RTUID", "WDS"."RTU"."RTUNAME",
(select RTUID from "WDS"."RTU" where rownum = 1 order by RTUID desc ) as val
from "WDS"."RTU" 即返回 缺失右括号的错误