为模拟建两张表A(AID,A1,BID)B(BID,B1)两者在A表中实现一对多关系CREATE TABLE [dbo].[A] ( [AID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [A1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[B] ( [BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [B1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOinsert into A values('1','1','1'); insert into A values('1','2','2'); insert into A values('1','3','3'); insert into A values('2','4','1'); insert into A values('2','5','6'); insert into A values('3','6','7');insert into B values('1','DD'); insert into B values('2','EE'); insert into B values('3','FF'); insert into B values('6','GG'); insert into B values('7','HH');select A.AID,A.A1,A.BID,B.B1 from A,B,(select A.AID from A group by AID)as C where A.AID=C.AID and B.BID=A.BID and A.BID=(select min(BID) from A where AID=C.AID)
create table B ( BID nvarchar(10), [name] nvarchar(10), AID nvarchar(10) ) create table A ( ID nvarchar(10), [name] nvarchar(10) )select *from A select *from b insert A select '1','zhan1' union select '2','zhan2' union select '3','zhan3'insert b select '1','B1','1' union select '2','B2','1' union select '3','B3','3' union select '4','B4','3'select A.*,c.* from A left join (select * from B where BID in(select min(BID) as BID from B group by AID)) as c on A.ID = c.AID
CREATE TABLE [dbo].[A] ( [AID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [A1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[B] ( [BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [B1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOinsert into A values('1','1','1'); insert into A values('1','2','2'); insert into A values('1','3','3'); insert into A values('2','4','1'); insert into A values('2','5','6'); insert into A values('3','6','7');insert into B values('1','DD'); insert into B values('2','EE'); insert into B values('3','FF'); insert into B values('6','GG'); insert into B values('7','HH'); GO --写法很多 SELECT * FROM B x,A y WHERE x.BID=y.BID AND NOT EXISTS(SELECT 1 FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x INNER JOIN A y ON x.BID=y.BID AND NOT EXISTS(SELECT 1 FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x INNER JOIN A y ON x.BID=y.BID AND 1>(SELECT COUNT(*) FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x INNER JOIN A y ON x.BID=y.BID AND AID=(SELECT MAX(AID) FROM A WHERE BID=y.BID)SELECT x.*,y.* FROM B x INNER JOIN A y ON x.BID=y.BID INNER JOIN (SELECT MAX(AID) MA,BID FROM A GROUP BY BID) c ON x.BID=c.BID AND y.AID=MASELECT x.*,y.* FROM B x INNER JOIN A y ON x.BID=y.BID AND AID=(SELECT TOP 1 AID FROM A WHERE BID=y.BID ORDER BY AID)SELECT x.*,y.* FROM B x,A y,(SELECT MAX(AID) MA,BID FROM A GROUP BY BID) c WHERE x.BID=y.BID AND x.BID=c.BID AND y.AID=MA--组合出来的写法有几十种,手累了,不一一敲了 /*结果都是 BID B1 AID A1 BID 1 DD 2 4 1 2 EE 1 2 2 3 FF 1 3 3 6 GG 2 5 6 7 HH 3 6 7*/DROP TABLE A,B GO
select A.*,B.* FROM A INNER JOIN B ON A.ID=B.ParentID WHERE B ID IN (SELECT MAX(ID) FROM B GROUP BY B.ParentID)
[AID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[A1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[B] (
[BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[B1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into A values('1','1','1');
insert into A values('1','2','2');
insert into A values('1','3','3');
insert into A values('2','4','1');
insert into A values('2','5','6');
insert into A values('3','6','7');insert into B values('1','DD');
insert into B values('2','EE');
insert into B values('3','FF');
insert into B values('6','GG');
insert into B values('7','HH');select A.AID,A.A1,A.BID,B.B1 from A,B,(select A.AID from A group by AID)as C
where A.AID=C.AID and B.BID=A.BID and A.BID=(select min(BID) from A where AID=C.AID)
(
BID nvarchar(10),
[name] nvarchar(10),
AID nvarchar(10)
)
create table A
(
ID nvarchar(10),
[name] nvarchar(10)
)select *from A
select *from b
insert A select '1','zhan1'
union
select '2','zhan2'
union
select '3','zhan3'insert b select '1','B1','1'
union
select '2','B2','1'
union
select '3','B3','3'
union
select '4','B4','3'select A.*,c.* from A left join (select *
from B
where BID in(select min(BID) as BID from B group by AID)) as c
on A.ID = c.AID
[AID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[A1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[B] (
[BID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[B1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into A values('1','1','1');
insert into A values('1','2','2');
insert into A values('1','3','3');
insert into A values('2','4','1');
insert into A values('2','5','6');
insert into A values('3','6','7');insert into B values('1','DD');
insert into B values('2','EE');
insert into B values('3','FF');
insert into B values('6','GG');
insert into B values('7','HH');
GO
--写法很多
SELECT * FROM B x,A y WHERE x.BID=y.BID
AND NOT EXISTS(SELECT 1 FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x
INNER JOIN A y
ON x.BID=y.BID AND NOT EXISTS(SELECT 1 FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x
INNER JOIN A y
ON x.BID=y.BID AND 1>(SELECT COUNT(*) FROM A WHERE BID=y.BID AND AID>y.AID)SELECT * FROM B x
INNER JOIN A y
ON x.BID=y.BID AND AID=(SELECT MAX(AID) FROM A WHERE BID=y.BID)SELECT x.*,y.* FROM B x
INNER JOIN A y
ON x.BID=y.BID
INNER JOIN (SELECT MAX(AID) MA,BID FROM A GROUP BY BID) c
ON x.BID=c.BID AND y.AID=MASELECT x.*,y.* FROM B x
INNER JOIN A y
ON x.BID=y.BID AND AID=(SELECT TOP 1 AID FROM A WHERE BID=y.BID ORDER BY AID)SELECT x.*,y.* FROM B x,A y,(SELECT MAX(AID) MA,BID FROM A GROUP BY BID) c
WHERE x.BID=y.BID AND x.BID=c.BID AND y.AID=MA--组合出来的写法有几十种,手累了,不一一敲了
/*结果都是
BID B1 AID A1 BID
1 DD 2 4 1
2 EE 1 2 2
3 FF 1 3 3
6 GG 2 5 6
7 HH 3 6 7*/DROP TABLE A,B
GO