有两个表 a,b
a中字段: aid,others
b中字段: aid,progress,但是,填的值是a表中一个id只有一条数据,在b表中一个aid对应多条数据,
比如
a:19,aaa
b:19,12345
19,23456
19,1478
这样的,现在想读取a中的一条数据,和对应的b中的progress那一项值最大的一条数据,结果保存一个dataset。select top 1 * from a,b where a.aid=b.aid order by b.aid 我自己写的只能读取一条,不能读出所有数据,各位高手们帮帮忙啊,项目紧急,在线等......
a中字段: aid,others
b中字段: aid,progress,但是,填的值是a表中一个id只有一条数据,在b表中一个aid对应多条数据,
比如
a:19,aaa
b:19,12345
19,23456
19,1478
这样的,现在想读取a中的一条数据,和对应的b中的progress那一项值最大的一条数据,结果保存一个dataset。select top 1 * from a,b where a.aid=b.aid order by b.aid 我自己写的只能读取一条,不能读出所有数据,各位高手们帮帮忙啊,项目紧急,在线等......
from a,b as c where a.aid=c.aid
and not exists(select 1 from b where aid=c.aid and progress>b.progress)
from a
cross apply]
(select top 1 * from b where aid=a.aid order by progress desc) as c或這樣
as
(select aid,
progress,
px=row_number()over(partition by aid order by progress desc)
from b)
select *
from a,tt
where a.aid=tt.aid and b.px=1
(SELECT MAX(progress) progress FROM B WHERE A.AID=B.AID) PROGRESS
FROM A当你A表不多,B表数量大于A表较多的情况下,这种子查询的方式会高于连接
;with tt
as
(select aid,
progress,
px=row_number()over(partition by aid order by progress desc)
from b)
select *
from a,tt
where a.aid=tt.aid and tt.px=1
go
set nocount on
if OBJECT_ID('A','U') is not null drop table A
go
if OBJECT_ID('B','U') is not null drop table B
gocreate table B(bid int,number int)
go
create table A(aid int,name nvarchar(10))
go
insert into A
select 19,'aaa'
go
insert into B
select 19,12345 union all
select 19,23456 union all
select 19,25
goselect * from
(select * from A)as tba
outer apply
(
select top 1 * from B where tba.aid=b.bid order by number desc
)as tbb
/*
aid name bid number
----------- ---------- ----------- -----------
19 aaa 19 23456
*/
*
from
a,b
where
a.aid=c.aid
and
not exists(select 1 from b t where aid=c.aid and progress>b.progress)
*
from
a,b
where
a.aid=b.aid
and
progress=(select max(progress) from b t where aid=b.aid)
*
from
a,b
where
a.aid=b.aid
and
progress=(select max(progress) from b t where aid=b.aid)--括号掉外面去了 呵呵
if OBJECT_ID('A','U') is not null drop table A
go
if OBJECT_ID('B','U') is not null drop table B
gocreate table A(aid int,others NVARCHAR(50))
go
create table B(aid int,progress int)
go
insert into A
select 19,'aaa'
go
insert into B
select 19,12345 union all
select 19,2345
GO
select
c.*
from a
cross apply
(select top 1 * from b where aid=a.aid order by progress desc) as c/*
aid progress
19 12345
*/select * from a,b as c where a.aid=c.aid and not exists(select 1 from b where aid=c.aid and progress>c.progress)
/*
aid others aid progress
19 aaa 19 12345
*/