SELECT c.*, e.name, group_concat(e.name) AS signPerson FROM mapping AS m LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m2.eid=e.id WHERE m.eid = 1 GROUP BY c.id;
SELECT c.*, e.name, group_concat(e2.name) AS signPerson FROM mapping AS m LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m.eid=e.id LEFT JOIN employee AS e2 ON m2.eid=e2.id WHERE m.eid = 1 GROUP BY c.id;
而且好象主表的选择有问题,应该是FROM mapping. group_concat(e.name),那既然只有选择某个人,那group_concat(e.name)不就没有意义了?select c.*,e.name from mapping as m left join employee as e on m.eid=e.id left join contract on m.cid=c.cid where e.eid='1' order by e.id
查询结果explain后的结果是 查询员工 所签的每个合同包含的员工数的总和。 效率应该可以。 楼主看下:P 另外,group_concat的替代方法在mysql中可能很难找。。 只能不group by 而用order by ,然后在程序里组合字符串SELECT c.*, e.name, e2.name FROM mapping AS m LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m.eid=e.id LEFT JOIN employee AS e2 ON m2.eid=e2.id WHERE m.eid = 1 ORDER BY c.id; 仅仅是我能想到的哈。:P
FROM mapping AS m
LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m2.eid=e.id
WHERE m.eid = 1
GROUP BY c.id;
FROM mapping AS m
LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m.eid=e.id LEFT JOIN employee AS e2 ON m2.eid=e2.id WHERE m.eid = 1
GROUP BY c.id;
group_concat(e.name),那既然只有选择某个人,那group_concat(e.name)不就没有意义了?select c.*,e.name from mapping as m left join employee as e on m.eid=e.id left join contract on m.cid=c.cid where e.eid='1' order by e.id
效率应该可以。
楼主看下:P
另外,group_concat的替代方法在mysql中可能很难找。。
只能不group by 而用order by ,然后在程序里组合字符串SELECT c.*, e.name, e2.name FROM mapping AS m
LEFT JOIN contract AS c ON c.id=m.cid LEFT JOIN mapping AS m2 ON c.id=m2.cid LEFT JOIN employee AS e ON m.eid=e.id LEFT JOIN employee AS e2 ON m2.eid=e2.id WHERE m.eid = 1
ORDER BY c.id;
仅仅是我能想到的哈。:P
我的目的是得到该员工的合同列表,如果一个合同有二个以上的签单人,
那么m.eid!=1的人员就被过滤了,也就是说本来这个人也有签单的份,列表中的签单人却没人他,举个例子
----mapping表----
eid cid
1 1
2 1
2 2
1 3
----employee表----
id name
1 小王
2 小李
----contract表----
id project signTime customer
1 建站 2005-11-29 CSDN
2 推广 2006-01-02 CSDN
3 Google 2006-02-13 CSDN
-----------------------
不指定条件的结果应该是这样客户名称 项目 签单时间 签单人
CSDN 建站 2005-03-03 小王,小李
CSDN 推广 2006-01-02 小李
CSDN Google 2006-02-13 小王
------------------------
指定条件后(假如查eid=1的,即小王)客户名称 项目 签单时间 签单人
CSDN 建站 2005-03-03 小王
CSDN Google 2006-02-13 小王可以看到本来小李也参与了建站的项目,却没显示出来,
因为where m.eid=1的时候把m表中的 eid=2 cid=1这条记录过滤了,
group_concat的时候无论什么合同都只有变成只有小王一人了,
这就是问题。
仅通过join多个m和e表是不能解决所有问题的。
那这样的话,估计只有通过PHP来实现了。group_concat没办法拿来做条件判断。