sql 子查詢變為連結查詢

CREATE TABLE `f` ( --父表

`ID` int(11) NOT NULL auto_increment,

`FDes` varchar(20) default NULL,

`deleted` int(11) default NULL,

`IDD` varchar(20) default NULL, --对应的外键

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `s` ( --子表

`ID` int(11) NOT NULL auto_increment,

`SDes` varchar(20) default NULL,

`FID` varchar(20) default NULL, -- 外键

`deleted` int(11) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

如果没有条件限制

select SDes,

(select FDes from f where IDD = FID limit 0, 1) as t

from s

等价于

select s.SDes,f.FDes

from

s left join f as f

on s.FID=f.IDD

如果有条件限制

select SDes,

(select FDes from f where IDD = FID and deleted=0 limit 0, 1) as ttt

from s where deleted=0

等价于

select s.SDes,f.FDes

from

s left join (select * from f where deleted=0 group by IDD) as f

on s.FID=f.IDD

where s.deleted=0

以上 `IDD` 和 `FID` 字段允许有重复内容

不过如果遇到类似问题还以实际情况为依据..