现有表A
ID Name
--------
1 aaa
2 bbb
3 ccc表B
ID Name Value Date Time
------------------------
1 aaa 1.017 20060506 162341
2 ccc 1.025 20060504 104253
3 bbb 2.014 20060404 091523
4 ccc 1.052 20060301 081524
5 aaa 1.034 20060506 094232
6 bbb 1.225 20060302 103214我希望通过执行SQL得到下面结果ID Name Value
---------------
1 aaa 1.017
2 bbb 2.014
3 ccc 1.025即查询出所有Name所对应的最新的Value
ID Name
--------
1 aaa
2 bbb
3 ccc表B
ID Name Value Date Time
------------------------
1 aaa 1.017 20060506 162341
2 ccc 1.025 20060504 104253
3 bbb 2.014 20060404 091523
4 ccc 1.052 20060301 081524
5 aaa 1.034 20060506 094232
6 bbb 1.225 20060302 103214我希望通过执行SQL得到下面结果ID Name Value
---------------
1 aaa 1.017
2 bbb 2.014
3 ccc 1.025即查询出所有Name所对应的最新的Value
A.ID,
A.Name,
B.Value
From
A
Inner Join
B
On A.Name = B.Name
Inner Join
(Select Name, Max([Date] + [Time] )As [DateTime] From B Group By Name) C
On B.Name = C.Name And B.[Date] + B.[Time] = C.[DateTime]
where 表a.id=表b.id and 表a.name=表b.name order by 表a.id
这个语句还有点小问题,就是当时间完全一样时
统计出来的结果就会出错,Name不惟一了
就是在这种情况下还需要去掉其它相同的记录
A.ID,
A.Name,
B.Value
From
A
Inner Join
B
On A.Name = B.Name
Inner Join
(
Select
Name, Max(ID) As ID
From
B
Inner Join
(Select Name, Max([Date] + [Time] )As [DateTime] From B Group By Name) C
On B.Name = C.Name And B.[Date] + B.[Time] = C.[DateTime]
Group By Name) D
On B.Name = D.Name And B.ID = D.ID
From B,(Select Name,max(Date+Time) as Date From B Group by Name) Table1
Where B.Date+B.Time=Table1.Date
Order By B.Name
------------------------------
1 aaa 1.017 20060506 162341
2 bbb 2.014 20060404 091523
3 ccc 1.025 20060504 104253
http://community.csdn.net/Expert/topic/5415/5415174.xml?temp=.1697962
Select
A.ID,
A.Name,
B.Value,
B.[Date],
B.[Time]
From
A
Inner Join
#T B
On
A.Name = B.Name
Inner Join
(Select Name, Max(OrderID) As OrderID From #T Group By Name) C
On B.Name = C.Name And B.OrderID = C.OrderIDDrop Table #T