sorry:select * from b where b.appdocid in (select top 2 id from a)
楼上的,这样不行吧我需要得到的是A表的doctitle和B表的appendfile 两个字段
select a.*,b.* from a,b where a.id=b.appdocid and b.appdocid in (select top 2 id from a)
select a.doctitle,b.appendfile from a,b where a.id=b.appdocid and b.appdocid in (select top 2 id from a order by ID desc)我觉得可以
create table table1 (ID int, doctitle varchar(20), docchannel varchar(20))create table table2 (appendid int, appendfile varchar(20), appdocid int) insert table1 (doctitle,docchannel) values ('新闻1','抗日') insert table2 (appendfile,appdocid) values ('抗日图片1',1) insert table2 (appendfile,appdocid) values ('抗日图片2',1) insert table2 (appendfile,appdocid) values ('抗日图片3',1)insert table1 (doctitle,docchannel) values ('新闻2','灭日') insert table2 (appendfile,appdocid) values ('灭日图片1',2) insert table2 (appendfile,appdocid) values ('灭日图片2',2) insert table2 (appendfile,appdocid) values ('灭日图片3',2) select top 2 doctitle,docchannel,(select max(appendid) from table2 WHERE a.ID = APPDOCID ) from table1 a order by ID desc doctitle docchannel appendid 新闻2 灭日 6 新闻1 抗日 3
我测试过了,你把其中的“D_B 换成 B,D_A换成A” SELECT TOP 2 appendid, appendfile, appdocid FROM dbo.D_B WHERE (appendid IN (SELECT MAX(appendid) AS appid FROM (SELECT dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel, dbo.D_B.appendid, dbo.D_B.appendfile, dbo.D_B.appdocid FROM dbo.D_A RIGHT OUTER JOIN dbo.D_B ON dbo.D_A.ID = dbo.D_B.appdocid) DERIVEDTBL GROUP BY appdocid)) ORDER BY appdocid DESC
--这样可能更符合你的要求 SELECT TOP 2 dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel, dbo.D_B.appendid, dbo.D_B.appendfile FROM dbo.D_B INNER JOIN dbo.D_A ON dbo.D_B.appdocid = dbo.D_A.ID WHERE (dbo.D_B.appendid IN (SELECT MAX(appendid) AS appid FROM (SELECT dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel, dbo.D_B.appendid, dbo.D_B.appendfile, dbo.D_B.appdocid FROM dbo.D_A RIGHT OUTER JOIN dbo.D_B ON dbo.D_A.ID = dbo.D_B.appdocid) DERIVEDTBL GROUP BY appdocid)) ORDER BY dbo.D_B.appdocid DESC
create table table1(ID int IDENTITY(1,1), doctitle varchar(20), docchannel varchar(20)) create table table2(appendid int IDENTITY(1,1), appendfile varchar(20), appdocid int)insert table1 (doctitle,docchannel) values ('新闻1','抗日') insert table2 (appendfile,appdocid) values ('抗日图片1',1) insert table2 (appendfile,appdocid) values ('抗日图片2',1) insert table2 (appendfile,appdocid) values ('抗日图片3',1) insert table1 (doctitle,docchannel) values ('新闻2','灭日') insert table2 (appendfile,appdocid) values ('灭日图片1',2) insert table2 (appendfile,appdocid) values ('灭日图片2',2) insert table1 (doctitle,docchannel) values ('新闻3','灭日胜利') insert table2 (appendfile,appdocid) values ('灭日胜利图片1',3) insert table2 (appendfile,appdocid) values ('灭日胜利图片2',3) insert table2 (appendfile,appdocid) values ('灭日胜利图片3',3) insert table2 (appendfile,appdocid) values ('灭日图片3',2) SELECT aa.doctitle,aa.docchannel,bb.appendfile FROM table1 aa, table2 bb, (select top 2 MAX(appendid) maxappendid FROM table2 GROUP BY appdocid ORDER BY MAX(appendid) DESC) cc WHERE aa.ID=bb.appdocid and bb.appendid=cc.maxappendid ORDER BY bb.appendid DESC
(select top 2 id from a)
and b.appdocid in
(select top 2 id from a)
and b.appdocid in
(select top 2 id from a order by ID desc)我觉得可以
(ID int,
doctitle varchar(20),
docchannel varchar(20))create table table2
(appendid int,
appendfile varchar(20),
appdocid int)
insert table1
(doctitle,docchannel)
values
('新闻1','抗日')
insert table2
(appendfile,appdocid)
values
('抗日图片1',1)
insert table2
(appendfile,appdocid)
values
('抗日图片2',1)
insert table2
(appendfile,appdocid)
values
('抗日图片3',1)insert table1
(doctitle,docchannel)
values
('新闻2','灭日')
insert table2
(appendfile,appdocid)
values
('灭日图片1',2)
insert table2
(appendfile,appdocid)
values
('灭日图片2',2)
insert table2
(appendfile,appdocid)
values
('灭日图片3',2)
select top 2 doctitle,docchannel,(select max(appendid) from table2 WHERE a.ID = APPDOCID ) from table1 a order by ID desc
doctitle docchannel appendid
新闻2 灭日 6
新闻1 抗日 3
SELECT TOP 2 appendid, appendfile, appdocid
FROM dbo.D_B
WHERE (appendid IN
(SELECT MAX(appendid) AS appid
FROM (SELECT dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel,
dbo.D_B.appendid, dbo.D_B.appendfile, dbo.D_B.appdocid
FROM dbo.D_A RIGHT OUTER JOIN
dbo.D_B ON dbo.D_A.ID = dbo.D_B.appdocid) DERIVEDTBL
GROUP BY appdocid))
ORDER BY appdocid DESC
SELECT TOP 2 dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel, dbo.D_B.appendid,
dbo.D_B.appendfile
FROM dbo.D_B INNER JOIN
dbo.D_A ON dbo.D_B.appdocid = dbo.D_A.ID
WHERE (dbo.D_B.appendid IN
(SELECT MAX(appendid) AS appid
FROM (SELECT dbo.D_A.ID, dbo.D_A.doctitle, dbo.D_A.docchannel,
dbo.D_B.appendid, dbo.D_B.appendfile, dbo.D_B.appdocid
FROM dbo.D_A RIGHT OUTER JOIN
dbo.D_B ON dbo.D_A.ID = dbo.D_B.appdocid) DERIVEDTBL
GROUP BY appdocid))
ORDER BY dbo.D_B.appdocid DESC
create table table2(appendid int IDENTITY(1,1), appendfile varchar(20), appdocid int)insert table1 (doctitle,docchannel) values ('新闻1','抗日')
insert table2 (appendfile,appdocid) values ('抗日图片1',1)
insert table2 (appendfile,appdocid) values ('抗日图片2',1)
insert table2 (appendfile,appdocid) values ('抗日图片3',1)
insert table1 (doctitle,docchannel) values ('新闻2','灭日')
insert table2 (appendfile,appdocid) values ('灭日图片1',2)
insert table2 (appendfile,appdocid) values ('灭日图片2',2)
insert table1 (doctitle,docchannel) values ('新闻3','灭日胜利')
insert table2 (appendfile,appdocid) values ('灭日胜利图片1',3)
insert table2 (appendfile,appdocid) values ('灭日胜利图片2',3)
insert table2 (appendfile,appdocid) values ('灭日胜利图片3',3)
insert table2 (appendfile,appdocid) values ('灭日图片3',2)
SELECT aa.doctitle,aa.docchannel,bb.appendfile
FROM table1 aa,
table2 bb,
(select top 2 MAX(appendid) maxappendid
FROM table2 GROUP BY appdocid
ORDER BY MAX(appendid) DESC) cc
WHERE aa.ID=bb.appdocid and bb.appendid=cc.maxappendid
ORDER BY bb.appendid DESC