请教SQL语句 急!!!!
有两张表TABLE_A,TABLE_BTABLE_A
ID TIME NUMBER NAME_ID
1 2010-3-1 50 1
2 2010-3-4 60 2
3 2010-3-3 10 3
4 2010-3-2 15 3
5 2010-3-2 29 2
6 2010-3-3 88 1
TABLE_B
ID NAME
1 HAHA
2 HEHE
3 PAPA
其中TABLE_A中的NAME_ID是TABLE_B中的ID
现在需要取TABLE_A中时间最大那条记录的NUMBER,还要按照TABLE_B的NAME进行分组查询
显示结果是
NAME NUMBER
HAHA 88
HEHE 60
PAPA 10 --------------------编程问答-------------------- 分不多 谢谢帮忙 --------------------编程问答--------------------
create table #TABLE_A--------------------编程问答--------------------
(
ID int identity(1,1) primary key,
[TIME] datetime,
NUMBER int,
NAME_ID int
)
insert into #TABLE_A select '2010-3-1',50,1
insert into #TABLE_A select '2010-3-4',60,2
insert into #TABLE_A select '2010-3-3',10,3
insert into #TABLE_A select '2010-3-2',15,3
insert into #TABLE_A select '2010-3-2',29,2
insert into #TABLE_A select '2010-3-3',88,1
create table #TABLE_B
(
ID int identity(1,1) primary key,
Name nvarchar(30)
)
insert into #TABLE_B select 'HAHA'
insert into #TABLE_B select 'HEHE'
insert into #TABLE_B select 'PAPA'
select B.Name,A.NUMBER from
(
select * from #TABLE_A A where not exists
(select * from #TABLE_A where NAME_ID=A.NAME_ID and [TIME]>A.[TIME])
) A
join #TABLE_B B
on A.NAME_ID=B.ID
order by A.NUMBER desc
Name NUMBER
------------------------------ -----------
HAHA 88
HEHE 60
PAPA 10
(3 行受影响)
分不多可以加点
已经解决啦 --------------------编程问答-------------------- SELECT MAX(A.NUMBER) FROM TABLE_A AS A INNER JOIN TABLE_B AS B ON(A.NAME_ID=B.ID)
GROUP BU B.NAME
这样行不? --------------------编程问答-------------------- --------------------编程问答--------------------
已经在数据库中验证通过,完全正确 --------------------编程问答--------------------
自己多写点sql就会知道要怎么解决类似的问题 --------------------编程问答-------------------- select top 3, Name,NUMBER from table_A as a,table_B as b
where a. NAME_ID=b.id
group by Name,NUMBER
order by TIME decs --------------------编程问答-------------------- UP UP
补充:.NET技术 , C#