A表有一条数据
ANamealan
Alee
aspn
B表有对应的三条数据数据BNo-----BTell------Name----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1366666----alan----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1388888----alan----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1399999----alan----附属A---附属B---附属C---附属D---附属E-----....NO2-----1333333----Alee----附属A---附属B---附属C---附属D---附属E-----....NO3-----1311111----aspn----附属A---附属B---附属C---附属D---附属E-----....
NO3-----1322222----aspn----附属A---附属B---附属C---附属D---附属E-----....
备注平时用后台程序多次查询实现,取B表数据是用 SELECT TOP 1 BTell FROM B表 WHERE Name=@Name ORDER BY BNo DESC
用这种查询方式即出 降序过 的 第一条!现在要实现一条SQL
查询A表所有数据 连接 B表 (B表里的数据是经过ORDER BY DESC 降序过的)虽然B表的 BNo 都是一样的,没关系,用降序可以取到需要的数据!
ANamealan
Alee
aspn
B表有对应的三条数据数据BNo-----BTell------Name----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1366666----alan----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1388888----alan----附属A---附属B---附属C---附属D---附属E-----....
NO1-----1399999----alan----附属A---附属B---附属C---附属D---附属E-----....NO2-----1333333----Alee----附属A---附属B---附属C---附属D---附属E-----....NO3-----1311111----aspn----附属A---附属B---附属C---附属D---附属E-----....
NO3-----1322222----aspn----附属A---附属B---附属C---附属D---附属E-----....
备注平时用后台程序多次查询实现,取B表数据是用 SELECT TOP 1 BTell FROM B表 WHERE Name=@Name ORDER BY BNo DESC
用这种查询方式即出 降序过 的 第一条!现在要实现一条SQL
查询A表所有数据 连接 B表 (B表里的数据是经过ORDER BY DESC 降序过的)虽然B表的 BNo 都是一样的,没关系,用降序可以取到需要的数据!
select *
from a left join (select top 1 * from b order by ...) t on a.[] = t.[]
where ...
WHERE Name=@Name ORDER BY BNo DESC
left join (select top 1 * from b order by ...) t
on a.AName=t.Name
where Name=@Name ORDER BY BNo DESC
不行
select *
from a表 A left join (select top 1 * from b表 order by BNo) t on a.AName = T.Nameselect top 1 * from b表 order by BNo 只有行数据,其它的没有数据了
if OBJECT_ID('A', 'U') is not null
drop table A
create table A(ANAME varchar(20))
insert into A values('alan'),('Alee'),('aspn')if OBJECT_ID('B', 'U') is not null
drop table B
create table B
(
BNo varchar(10),
BTell varchar(20),
Name varchar(20)
)
insert into B values
('NO1','1366666','alan'),
('NO1','1388888','alan'),
('NO1','1399999','alan'),
('NO2','1333333','Alee'),
('NO3','1311111','aspn'),
('NO3','1322222','aspn')
SELECT * FROM B WHERE B.Name IN (SELECT ANAME FROM A) ORDER BY BNO DESC
--RESULTSET
BNo BTell Name
---------- -------------------- --------------------
NO3 1311111 aspn
NO3 1322222 aspn
NO2 1333333 Alee
NO1 1366666 alan
NO1 1388888 alan
NO1 1399999 alan
CREATE TABLE [dbo].[A表](
[AName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]GO
INSERT INTO [dbo].[A表]
SELECT
'alan'
INSERT INTO [dbo].[A表]
SELECT
'alee'
INSERT INTO [dbo].[A表]
SELECT
'aspn'
GO----------下面是B表------------
CREATE TABLE [dbo].[B表](
[BNo] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[BTell] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[附属A] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[附属B] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[附属C] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOINSERT INTO [dbo].[B表]
SELECT
'NO1', '1311111', 'alan' ,'a1', 'b1', 'c1'
INSERT INTO [dbo].[B表]
SELECT
'NO1', '1322222', 'alan' ,'a2', 'b2', 'c2'
INSERT INTO [dbo].[B表]
SELECT
'NO1', '1333333', 'alan' ,'a3', 'b3', 'c3'INSERT INTO [dbo].[B表]
SELECT
'NO2', '13444444', 'alee' ,'a4', 'b4', 'c4'INSERT INTO [dbo].[B表]
SELECT
'NO3', '13555555', 'aspn' ,'a5', 'b5', 'c5'
INSERT INTO [dbo].[B表]
SELECT
'NO3', '13666666', 'aspn' ,'a6', 'b6', 'c6'
select *
from a表 A left join (select top 1 * from b表 order by BNo) t on a.AName = T.Name
是想这样的,
我把SQL复制到上面了,
结果是只有A表的三条,取B表降序后的第一条alan----NO1-----1399999----alan----附属A---附属B---附属C---附属D---附属E-----....
Alee----NO2-----1333333----Alee----附属A---附属B---附属C---附属D---附属E-----....
aspn----NO3-----1322222----aspn----附属A---附属B---附属C---附属D---附属E-----....
create table A(ANAME varchar(20))
insert into A values('alan')
insert into A values('Alee')
insert into A values('aspn')
gocreate table B
(
BNo varchar(10),
BTell varchar(20),
Name varchar(20)
)
insert into B values('NO1','1366666','alan')
insert into B values('NO1','1388888','alan')
insert into B values('NO1','1399999','alan')
insert into B values('NO2','1333333','Alee')
insert into B values('NO3','1311111','aspn')
insert into B values('NO3','1322222','aspn')
goselect *
from a left join (select * from(select *,px = row_number() over (partition by name order by getdate())
from b)e where px = 1)t
on a.aname = t.namedrop table A,B
/*ANAME BNo BTell Name px
-------------------- ---------- -------------------- -------------------- --------------------
alan NO1 1366666 alan 1
Alee NO2 1333333 Alee 1
aspn NO3 1311111 aspn 1(3 行受影响)
create table A(ANAME varchar(20))
insert into A values('alan')
insert into A values('Alee')
insert into A values('aspn')
gocreate table B
(
BNo varchar(10),
BTell varchar(20),
Name varchar(20)
)
insert into B values('NO1','1366666','alan')
insert into B values('NO1','1388888','alan')
insert into B values('NO1','1399999','alan')
insert into B values('NO2','1333333','Alee')
insert into B values('NO3','1311111','aspn')
insert into B values('NO3','1322222','aspn')
goselect *
from a left join (select * from(select *,px = row_number() over (partition by bno order by BTell desc)
from b)e where px = 1)t
on a.aname = t.namedrop table A,B
/*ANAME BNo BTell Name px
-------------------- ---------- -------------------- -------------------- --------------------
alan NO1 1399999 alan 1
Alee NO2 1333333 Alee 1
aspn NO3 1322222 aspn 1(3 行受影响)
create table A(ANAME varchar(20))
insert into A values('alan')
insert into A values('Alee')
insert into A values('aspn')
gocreate table B
(
BNo varchar(10),
BTell varchar(20),
Name varchar(20)
)
insert into B values('NO1','1366666','alan')
insert into B values('NO1','1388888','alan')
insert into B values('NO1','1399999','alan')
insert into B values('NO2','1333333','Alee')
insert into B values('NO3','1311111','aspn')
insert into B values('NO3','1322222','aspn')
goselect *
from a left join b on a.aname = b.name
where not exists (select 1 from b t where name = b.name and BTell > b.BTell)drop table A,B
/*ANAME BNo BTell Name
-------------------- ---------- -------------------- --------------------
alan NO1 1399999 alan
Alee NO2 1333333 Alee
aspn NO3 1322222 aspn(3 行受影响)
*
from
a left join b t
on
a.aname = t.name
where
BTELL=(select max(BTell) from b where name = t.name )
--用OUTER APPLY
select b.* FROM a表 a
OUTER APPLY
(select TOP(1) * from b表 WHERE [Name] = a.[AName] ORDER BY BNo desc) b
/*
NO1 1322222 alan a2 b2 c2
NO2 13444444 alee a4 b4 c4
NO3 13666666 aspn a6 b6 c6
*/