主页 / dba / 问题 / 105283
Accepted
Arpan Das
Asked: 2015-06-27 10:50:16 +0800 CST

mysql group_concat 的问题，复杂的查询，没有得到所需的结果

  • 772

我有 5 张桌子

  • 大学
  • 贸易
  • 区
  • 接触
  • 录取

他们的关系是这样的

表关系

我的联系表有问题。在这个表中，cid 和 location 是主键。每个cid（即大学ID）只能有两个位置的校园和办公室。表摄入量连接大学和贸易表。通过下面的查询，我得到每个地址两次的结果。这是我的查询：

SELECT cname, ctype, tname, seats, hostel, new, dname, address, ph_no, email, website FROM college, trade, district, intake, contact WHERE intake.cid = college.cid and intake.tid = trade.tid and college.did = district.did and contact.cid = college.cid ORDER BY cname ASC;

我目前的结果： 我目前的结果

我想要的结果： 要求的结果

我知道这可以通过 grop_concat 和 concat 函数来实现，但我不知道在这种情况下如何使用这些函数。有人请帮助我构建查询以实现此结果。

谢谢你。

mysql group-concatenation

3 个回答

  1. Best Answer

    它会是这样的：

    SELECT cname, ctype, tname, seats, hostel, dname,
GROUP_CONCAT(addr SEPARATOR '\n') addresses
FROM
(
    SELECT cname, ctype, tname, seats, hostel, dname,
    CONCAT('Address: ',address,',Phone:',ph_no,',Email:',email,',Website:',website) addr
    FROM college, trade, district, intake, contact
    WHERE intake.cid = college.cid
    and intake.tid = trade.tid
    and college.did = district.did
    and contact.cid = college.cid
    AND cname LIKE '%{$cname}%'
    AND tname LIKE '%{$tname}%' {$ctype}
    AND dname LIKE '%{$dname}%' {$hostel}
    ORDER BY cname ASC
) A GROUP BY cname, ctype, tname, seats, hostel, dname;

    由于group_concat_max_len的默认值为 1024，因此您可能需要再做两件事来容纳超过GROUP_CONCAT1024 的结果

    首先，登录 MySQL 并运行

    mysql> SET GLOBAL group_concat_max_len = 1024 * 1024;

    然后，将其添加到组标题my.cnf下[mysqld]

    [mysqld]
group_concat_max_len=1M

    试试看 ！！！

    • 0

  2. 这应该适合你：

    SELECT 
    cname, 
    ctype, 
    tname, 
    seats, 
    hostel, 
    dname, 
    GROUP_CONCAT(address SEPARATOR '\n') AS address, 
    ph_no, 
    email, 
    website
FROM college, trade, district, intake, contact
WHERE intake.cid = college.cid and intake.tid = trade.tid 
    and college.did = district.did and contact.cid = college.cid
GROUP BY cid
ORDER BY cname ASC;

    我补充说GROUP_CONCAT(address SEPARATOR '\n')，这使得在地址之间跳过一行并将GROUP BY cid其分组cid。

    • 0

  3. 解决了，查询是：

        $sql = "SELECT cname, ctype, tname, seats, dname, hostel,
            GROUP_CONCAT(addr SEPARATOR '<hr />') addresses
            FROM
            (
                SELECT cname, ctype, tname, seats, dname, hostel,
                CONCAT('<b>Address:</b> ', IFNULL(address, 'N/A'),
                        ',<br /><b>Phone:</b> ', IFNULL(ph_no, 'N/A'),
                        ',<br /><b>Email:</b> ', IFNULL(email, 'N/A'),
                        ',<br /><b>Website:</b> ', IFNULL(website, 'N/A')
                    ) addr
                FROM college, trade, district, intake, contact
                WHERE intake.cid = college.cid
                    AND intake.tid = trade.tid
                    AND college.did = district.did
                    AND contact.cid = college.cid
                    AND cname LIKE '%{$name}%' 
                    AND tname LIKE '%{$trade}%' 
                    {$type} 
                    AND dname LIKE '%{$district}%' 
                    {$hostel}
                ORDER BY cname ASC
            ) A GROUP BY cname, ctype, tname, seats, dname, hostel";

    不添加 IFNULL 会产生问题。最初由 RolandoMySQLDBA 回答。谢谢。

    • 0

相关问题