34,588
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE bbsimg( id CHAR(2),bbsid CHAR(2), img CHAR(10), con CHAR(10))
INSERT bbsimg
SELECT '01', '01' , '01.jpg', '我是谁' UNION ALL
SELECT '02', '01' , '02.jpg', '我是谁' UNION ALL
SELECT '03', '01' , '03.jpg', '我是谁' UNION ALL
SELECT '04', '01' , '04.jpg', '我是谁' UNION ALL
SELECT '06', '02' , '05.jpg', '风景图片' UNION ALL
SELECT '07', '02' , '06.jpg', '风景图片' UNION ALL
SELECT '08', '02' , '07.jpg', '风景图片'
CREATE TABLE BBS(ID CHAR(2),userID INT, classID CHAR(5))
INSERT BBS
SELECT '01', 0 , '摄影'UNION
SELECT '02',0, '风景'
--SELECT * FROM BBSIMG
--SELECT * FROM BBS
SELECT BBS.*,IMG FROM BBS,
(SELECT T1.BBSID ,IMG FROM BBSIMG,
(SELECT MIN(ID) ID ,BBSID FROM BBSIMG GROUP BY BBSID)AS T1
WHERE BBSIMG.ID=T1.ID)AS T2
WHERE BBS.ID=T2.BBSID
select a.*,b.img from bbs a
join
(select bbsid,img from bbsimg a where id
= select min(ID) from bbsimg where a.bbsid=bbsid) b
on a.id=b.bbisd
select
a.id,
a.userid,
a.classid,
b.img
from
bbs a
left join
bbsimg b
on
a.id=b.bbsid
and
not exists(select 1 from bbsimg where bbsid=b.bbsid and id<b.id)
select
a.id,
a.userid,
a.classid,
b.img
from
bbs a
left join
bbsimg b
on
a.id=b.bbsid
and
not exists(select 1 from bbsimg where bbsid=b.bbsid and img<b.img)