1.有两个表:Op(仪器表)、Vp(仪器数值表)2.Op表中的字段为:Id(Int自增,主键),Name(Varchar100),Code(Varchar100)
Op表中的数据如下:
1 1号仪表 AE09B
2 2号仪表 6C9A23.Vp表中的字段为:Id(Int自增,主键),OpId(int与Op表中的Id对应的外键),value(float,对应的值)Vp表中的数据如下:
1 1 21.5
2 2 101.1
3 1 23.2
4 2 105.6
5 1 22.8
6 2 104.34.现在我想查出Vp表中ID最大的(也就是最新的)1号仪表与2号仪表的所有相关值结果,如下:
1 1号仪表 AE09B 5 22.8
2 2号仪表 6C9A2 6 104.35.以下是我写的SQL语句,很不对呀,请指教!SELECT o.id, o.name, o.code, v.id as vid, v.value
FROM Op o ,Vp v
WHERE o.id = v.Opid and v.id in(select max(id) from Vp)
Op表中的数据如下:
1 1号仪表 AE09B
2 2号仪表 6C9A23.Vp表中的字段为:Id(Int自增,主键),OpId(int与Op表中的Id对应的外键),value(float,对应的值)Vp表中的数据如下:
1 1 21.5
2 2 101.1
3 1 23.2
4 2 105.6
5 1 22.8
6 2 104.34.现在我想查出Vp表中ID最大的(也就是最新的)1号仪表与2号仪表的所有相关值结果,如下:
1 1号仪表 AE09B 5 22.8
2 2号仪表 6C9A2 6 104.35.以下是我写的SQL语句,很不对呀,请指教!SELECT o.id, o.name, o.code, v.id as vid, v.value
FROM Op o ,Vp v
WHERE o.id = v.Opid and v.id in(select max(id) from Vp)
from op m, vp n
where m.id = n.opid
and n.id = (select max(id) from vp where opid = n.opid)select m.* , m.id , m.value
from op m, vp n
where m.id = n.opid
and not exists (select 1 from vp where opid = n.opid and id > n.id)
a.*,b.OpId,b.value
from
Op a ,Vp b
where
a.id = b.Opid
and
v.id =(select max(id) from vp)
a.*,b.OpId,b.value
from
Op a ,Vp b
where
a.id = b.Opid
and
v.id =(select max(id) from vp where opid = b.opid)
(select max(id) from Vp where OpId=t.id),
(select top 1 value from Vp where OpId=t.id order by id desc)
from Op t
FROM Op o ,Vp v
WHERE o.id = v.Opid and v.id in(select max(id) from Vp where Opid=v.Opid group by OpId )
create table op(Id int,Name varchar(10),Code varchar(10))
insert into op values(1 , '1号仪表' , 'AE09B')
insert into op values(2 , '2号仪表' , '6C9A2')
create table vp(Id int,OpId int,value decimal(18,1))
insert into vp values(1 , 1 , 21.5 )
insert into vp values(2 , 2 , 101.1 )
insert into vp values(3 , 1 , 23.2 )
insert into vp values(4 , 2 , 105.6 )
insert into vp values(5 , 1 , 22.8 )
insert into vp values(6 , 2 , 104.3 )
goselect m.* , n.id , n.value
from op m, vp n
where m.id = n.opid
and n.id = (select max(id) from vp where opid = n.opid)select m.* , n.id , n.value
from op m, vp n
where m.id = n.opid
and not exists (select 1 from vp where opid = n.opid and id > n.id) drop table op , vp/*
Id Name Code id value
----------- ---------- ---------- ----------- --------------------
1 1号仪表 AE09B 5 22.8
2 2号仪表 6C9A2 6 104.3(所影响的行数为 2 行)Id Name Code id value
----------- ---------- ---------- ----------- --------------------
1 1号仪表 AE09B 5 22.8
2 2号仪表 6C9A2 6 104.3(所影响的行数为 2 行)
*/
--or
select t.*,r.id,r.value
from Op t , Vp r
where t.id = r.OpId
and r.Id in (select max(id) from Vp where OpId=t.OpId)
FROM Op o ,Vp v
WHERE o.id = v.Opid and v.id = (select max(id) from Vp where Opid=v.Opid)
FROM Op o ,Vp v
WHERE V.Opid=O.id and v.id in(select max(id) from Vp WHERE )
FROM Op o ,Vp v
WHERE V. and v.id in(select max(id) from Vp WHERE Opid=O.id )