Mysql查询组合

Question

我有一个由3个表a，b，c组合而成的查询，这里的问题是表c存在于两个数据库D1和D2中，所以我必须从两个数据库中获取表c .Table a和b仅从数据库D1中的记录，

查询为：

$strQuery="SELECT a.id, b.lastname, school " ."FROM D1.a,D1.b" .
    " WHERE a.idint = (select c_idint from D1.c where Cid IN(10) 
UNION select c_idint from D2.c where Cid IN(10) order by c_idint)
 AND a.idint = b.idb";

问题是字段'school‘来自表c，如果我从查询中避免了字段'school’，它可以工作得很好，但我也必须获取字段'school‘，我如何修改查询。谢谢：)

Answer 1

试试这个：

$strQuery="SELECT a.id, b.lastname, c.school as dSchool, '' as ddSchool " ."FROM D1.a,D1.b, D1.c" .
    " WHERE a.idint = c.idint AND c.Cid IN (10)
 AND a.idint = b.idb

UNION 

SELECT a.id, b.lastname, '' as dSchool, c.school as ddSchool " ."FROM D1.a,D1.b, D2.c" .
    " WHERE a.idint = c.idint AND c.Cid IN (10)
 AND a.idint = b.idb
";

Answer 2

你需要的是alias，

$strQuery="SELECT a.id, b.lastname, D2.c.school as school1,D1.c.school as school2 " ."FROM D1.a,D1.b,D1.c,d2.c" .
          " WHERE a.idint = (select c_idint from D1.c where Cid IN(10) 
          UNION select c_idint from D2.c where Cid IN(10) order by c_idint)
          AND a.idint = b.idb";

Answer 3

像这样试一试：

$strQuery="SELECT a.id, b.lastname, c.school " ."FROM D1.a,D1.b,D1.c" .
 " WHERE a.idint = (select c_idint from D1.c where Cid IN(10) 
UNION select c_idint,school from D2.c where Cid IN(10) order by c_idint)
AND a.idint = b.idb";