用sql JOIN合并两个表？

Question

可能的重复：
将多个数据库表组合在一起？

我想从两个数据库表中获得如下输出：

伊斯坦布尔一周游_1 | 88888 99999 $ 112233 $ 445566 |两天三夜| 15:29
伊斯坦布尔一周游_2 | 55555& 66666 $ 77777 |两天三夜| 12:03
伊斯坦布尔一周游_3 | 11111& 22222 美元 33333 美元 44444 |两天三夜| 12:03

这些是我的表格：

更新：

它给了我这个输出：http://img708.imageshack.us/img708/2404/outputnz.gif 但如果我输入 $find 值“55555”，输出如下： http://img528.imageshack.us/img528/3576/outputnow.gif 但我想要的是： http://img832.imageshack.us/img832/120/outputwant.gif 。如何？

$query = $this -> db -> query('
    SELECT
       @rownum := @rownum + 1 rownum,
       tour_foreign.id, 
       tour_foreign.name, 
       MIN(tour_foreign_residence.name_re) AS name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term,
       tour_foreign.useradmin_submit,
       tour_foreign.date_submit,
       GROUP_CONCAT( tour_foreign_residence.name_re 
                     ORDER BY tour_foreign_residence.name_re 
                     SEPARATOR " $ "
                   ) AS name_re_all
    FROM   tour_foreign 
      INNER JOIN tour_foreign_residence 
        ON ( tour_foreign.id = tour_foreign_residence.relation )
      JOIN (SELECT @rownum := 0) r
    WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re_all LIKE "%' . $find . '%"
    GROUP BY  tour_foreign.id ');

我从上面的sql得到以下错误：

发生数据库错误
错误号：1054

“where”中的未知列“tour_foreign_residence.name_re_all” 条款'

SELECT @rownum := @rownum + 1 rownum,tour_foreign.id, tour_foreign.name, MIN(tour_foreign_residence.name_re) AS name_re, tour_foreign.service、tour_foreign.date_go、tour_foreign.date_back、 tour_foreign.term，tour_foreign.useradmin_submit， tour_foreign.date_submit, GROUP_CONCAT(tour_foreign_residence.name_re ORDER BYtour_foreign_residence.name_re SEPARATOR " $ " ) AS name_re_all FROMtour_foreign INNER JOINtour_foreign_residence ON ( tour_foreign.id =tour_foreign_residence.relation ) JOIN (选择 @rownum := 0) r WHEREtour_foreign.name LIKE "%%" OR tour_foreign_residence.name_re_all LIKE "%%" 分组依据 tour_foreign.id

文件名： D:\xampp\htdocs\system\database\DB_driver.php

行号：330

Answer 1

您需要按某些内容进行分组，否则聚合 GROUP_CONCAT() 会将所有行收集到一个中：

GROUP BY  tour_foreign.id 

使用：

$query = $this -> db -> query('
    SELECT
       @rownum := @rownum + 1 rownum,
       tour_foreign.id, 
       tour_foreign.name, 
       MIN(tour_foreign_residence.name_re) AS name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term,
       tour_foreign.useradmin_submit,
       tour_foreign.date_submit,
       GROUP_CONCAT( tour_foreign_residence.name_re 
                     ORDER BY tour_foreign_residence.name_re 
                     SEPARATOR " $ "
                   ) AS name_re_all
    FROM   tour_foreign 
      INNER JOIN tour_foreign_residence 
        ON ( tour_foreign.id = tour_foreign_residence.relation )
      JOIN (SELECT @rownum := 0) r
    WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
    GROUP BY  tour_foreign.id ');

Answer 2

尝试在 SQL 语句末尾添加 GROUP BY 子句。

Answer 3

SELECT a.name, GROUP_CONCAT(b.name_re ORDER BY b.name_re SEPARATOR " & "), a.term, a.time_go 
FROM tour_foreign a INNER JOIN tour_foreign residence b
ON a.id = b.relation
GROUP BY a.name

我刚刚学习 SQL，所以我把你的问题作为练习。希望它有效。