select case when a.dptid=0 then b.depname='' end,a,code,a.name b.depname from a,b where a.deptid=b.id
select case when a.emdepid=0 then b.depname='' end, a.id,a.emno,a.emname,a.emsex,b.depname from `c_employee` a,`c_department` b where a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15 这是运行后的sql语句 还是不行 没查出来数据何解?
select IF(a.dptid=0,'',b.depname),a,code,a.name b.depname from a INNER JOIN b ON a.deptid=b.id
贴建表及插入记录的SQL,及要求结果出来看看
CREATE TABLE `c_employee` ( `id` int(11) NOT NULL auto_increment, `emname` varchar(100) character set gb2312 NOT NULL, `emdepid` int(11) NOT NULL, `emno` varchar(50) character set gb2312 NOT NULL, `emsex` varchar(2) character set gb2312 NOT NULL, `emlinno` varchar(18) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1CREATE TABLE `c_department` ( `id` int(11) NOT NULL auto_increment, `depname` varchar(100) character set gb2312 NOT NULL, `depmemo` varchar(300) character set gb2312 NOT NULL, `istop` tinyint(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1select IF(a.emdepid=0,'',b.depname),a.id,a.emno,a.emname,a.emsex,b.depnamefrom `c_employee` a inner join `c_department` b on a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15
插入记录的SQL呢? select IF(a.emdepid=0,'',b.depname),a.id,a.emno,a.emname,a.emsex,b.depnamefrom `c_employee` a inner join `c_department` b on a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15 结果是否正确
不行 不过已经解决了,sql语句如下, 用的左连接 select a.id,a.emno,a.emname,a.emsex,b.depname from `c_employee` a left join `c_department` b on a.emdepid=b.id where a.emname='223’
b.depname
from a,b
where a.deptid=b.id
select case when a.emdepid=0 then b.depname='' end, a.id,a.emno,a.emname,a.emsex,b.depname from `c_employee` a,`c_department` b where a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15
这是运行后的sql语句 还是不行 没查出来数据何解?
b.depname from a INNER JOIN b ON a.deptid=b.id
贴建表及插入记录的SQL,及要求结果出来看看
`id` int(11) NOT NULL auto_increment,
`emname` varchar(100) character set gb2312 NOT NULL,
`emdepid` int(11) NOT NULL,
`emno` varchar(50) character set gb2312 NOT NULL,
`emsex` varchar(2) character set gb2312 NOT NULL,
`emlinno` varchar(18) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1CREATE TABLE `c_department` (
`id` int(11) NOT NULL auto_increment,
`depname` varchar(100) character set gb2312 NOT NULL,
`depmemo` varchar(300) character set gb2312 NOT NULL,
`istop` tinyint(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1select IF(a.emdepid=0,'',b.depname),a.id,a.emno,a.emname,a.emsex,b.depnamefrom `c_employee` a inner join `c_department` b on a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15
select IF(a.emdepid=0,'',b.depname),a.id,a.emno,a.emname,a.emsex,b.depnamefrom `c_employee` a inner join `c_department` b on a.emdepid=b.id and a.emname='223' order by a.emno,b.depname limit 0,15
结果是否正确
不过已经解决了,sql语句如下, 用的左连接
select a.id,a.emno,a.emname,a.emsex,b.depname from `c_employee` a left join `c_department` b on a.emdepid=b.id where a.emname='223’