日期:2014-05-19  浏览次数:20803 次

关于数据查询的问题(续)
现有表A
Name
--------
aaa
bbb
ccc
ddd
eee

表B
Name   Value   Date   Time
------------------------
aaa     1.017   20060506   162341
ccc     1.025   20060504   104253
bbb     2.014   20060404   091523
ccc     1.052   20060301   081524
aaa     1.034   20060506   094232
bbb     1.225   20060302   103214
ddd     1.032   20060506   132544

我希望通过执行SQL得到下面结果

Name   Value   Date   Time
---------------------------
aaa     1.017   20060506   162341
bbb     2.014   20060404   091523
ccc     1.025   20060504   104253
ddd     1.032   20060506   132544
eee     0           NULL           NULL

即查询出所有Name所对应的最新的Value

应该要用到左联和内联

------解决方案--------------------
Select OrderID = Identity(Int, 1, 1) Into #T From B Order By Name, [Date], [Time]
Select
A.Name,
B.Value,
B.[Date],
B.[Time]
From
A
Left 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.OrderID

Drop Table #T
------解决方案--------------------
create table #a
(
[name] varchar(10)
)

insert into a
select 'aaa 'union all
select 'bbb 'union all
select 'ccc ' union all
select 'ddd ' union all
select 'eee '

create table #b
(
[name] varchar(10),
[value] numeric(18,3),
[date] varchar(8),
[time] varchar(6)
)

insert into #b
select 'aaa ' , 1.017, '20060506 ', '162341 ' union all
select 'ccc ', 1.025, '20060504 ', '104253 ' union all
select 'bbb ', 2.014, '20060404 ', '091523 ' union all
select 'ccc ', 1.052, '20060301 ', '081524 ' union all
select 'aaa ' , 1.034, '20060506 ', '094232 ' union all
select 'bbb ', 1.225, '20060302 ', '103214 ' union all
select 'ddd ', 1.032, '20060506 ', '132544 '


--生成临时表,目的是增加自增列
select identity(int,1,1) as rowid,* into #
from #b
order by [date],[time]

--最终结果输出
select a.[name],a.[value],a.[date],a.[time]
from # a inner join (select max(rowid) as rowid from # group by [name]) b
on (a.rowid=b.rowid)
order by a.[name]

-----------------------
结果:
name value date time
---------- -------------------- -------- ------
aaa 1.017 20060506 162341
bbb 2.014 20060404 091523
ccc 1.025 20060504 104253
ddd 1.032 20060506 132544

(所影响的行数为 4 行)
------解决方案--------------------
晕用的着这么麻烦吗?
呵呵
select a.name,ob.value,ob.date,ob.time from
a left join b ob on
a.name=ob.name and (ob.date+ob.time)=(select max(ob.date+ob.time) from b where b.name=a.name)

这里 ob.date+ob.time 用个函数把它转成日期类型.