开门见山!我有两个表:
Tbl_Dev Tbl_Data
No ID Para ID Time Data
1 a x1 a 2009-09-24 30
1 b x2 a 2009-09-23 28
2 c x3 b 2009-09-24 31
2 d x4 b 2009-09-23 32
c 2009-09-24 33
c 2009-09-23 29
d 2009-09-24 31
d 2009-09-23 20想查询出某一个指定No下所有ID的各自的最新数据(Data)和参数(Para)。如No=1时查询结果应该为
ID Para Time Data
a x1 2009-09-24 30
b x2 2009-09-24 31SQL查询语句应该怎么写呢?先谢过大家了!
Tbl_Dev Tbl_Data
No ID Para ID Time Data
1 a x1 a 2009-09-24 30
1 b x2 a 2009-09-23 28
2 c x3 b 2009-09-24 31
2 d x4 b 2009-09-23 32
c 2009-09-24 33
c 2009-09-23 29
d 2009-09-24 31
d 2009-09-23 20想查询出某一个指定No下所有ID的各自的最新数据(Data)和参数(Para)。如No=1时查询结果应该为
ID Para Time Data
a x1 2009-09-24 30
b x2 2009-09-24 31SQL查询语句应该怎么写呢?先谢过大家了!
from Tbl_Dev, Tbl_Data
where Tbl_Dev.ID = Tbl_Data.ID
and Tbl_Dev.No = 1
a 2009-09-24 30
a 2009-09-23 28
b 2009-09-24 31
b 2009-09-23 32 必须再加一个条件(Tbl_Data.Time= '2009-09-24')才可达到你的要求SELECT Tbl_Data.ID,Tbl_Data.Time,Tbl_Data.Data
FROM Tbl_Dev INNER JOIN
Tbl_Data ON Tbl_Dev.ID= Tbl_Data.ID
WHERE (Tbl_Dev.No = '1') AND (Tbl_Data.Time= '2009-09-24')
from Tbl_Dev,Tbl_Data,(select Id,max(Time) Time from Tbl_Data group by ID )s
where Tbl_Dev.ID=Tbl_Data.ID and s.ID=Tbl_Dev.ID and s.Time=Tbl_Data.Time and Tbl_Dev.No=1
- -# max(Time) Time 是别名啊 不用逗号的啊 要不你加中括号行了 这样 max([Time]) [Time]
from Tbl_Dev
left join Tbl_Data on Tbl_Dev.ID=Tbl_Data.ID
where Tbl_Dev.No=1
order by Tbl_Data.Time DESC
FROM A INNER JOIN
B ON A.A2 = B.A2
WHERE (A.A1 = '1') AND (B.A3 = CONVERT(varchar(10), GETDATE(), 120))
SELECT Tbl_Data.ID,Tbl_Data.Time,Tbl_Data.Data
FROM Tbl_Dev INNER JOIN
Tbl_Data ON Tbl_Dev.ID= Tbl_Data.ID
WHERE (Tbl_Dev.No = '1') AND (Tbl_Data.Time= CONVERT(varchar(10), GETDATE(), 120))