如何从多个表的 SQL 查询中获取一组数据? [英] How to get one set of data from a SQL query from multiple table?

问题描述

我的表结构是

TABLE_1
|------|------|-------|
|  ID  | NAME | PHONE | = 1 ROW
|------|------|-------|
TABLE_2
|------|------------|----------|
|  ID  | TABLE_1_ID | CATEGORY | = 5 ROW
|------|------------|----------|
TABLE_3
|------|------------|----------|
|  ID  | TABLE_1_ID | ADDRESS  | = 3 ROW
|------|------------|----------|

我正在处理上表.但是我的结果生成 1 行，但在 CATEGORY 单元格中显示 5 CATEGORY 但它重复了 3 次，在 ADDRESS 单元格中，3 个地址重复了 5 次，如下所示 -

I am working with the above table. But my result generating 1 row but in the CATEGORY cell its displaying 5 CATEGORY but it's repeating 3 times and in the ADDRESS cell the 3 addresses repeating 5 times, like below -

RESULT
|----|------|-------|----------------------------|------------------|
|    |      |       |                            | (ADR1,ADR2,ADR3) |
|    |      |       | (CAT1,CAT2,CAT3,CAT4,CAT5) | (ADR1,ADR2,ADR3) |
| ID | NAME | PHONE | (CAT1,CAT2,CAT3,CAT4,CAT5) | (ADR1,ADR2,ADR3) |
|    |      |       | (CAT1,CAT2,CAT3,CAT4,CAT5) | (ADR1,ADR2,ADR3) |
|    |      |       |                            | (ADR1,ADR2,ADR3) |
|----|------|-------|----------------------------|------------------|

我的 PHP+SQL 是

My PHP+SQL is

$db = new Database;
$getData = "SELECT a.name, a.phone, b.category, c.address
FROM table_1 a JOIN table_2 b JOIN table_3 c
WHERE a.id = b.table_1_id AND a.id = c.table_1_id
ORDER BY a.id ASC";
$execute = $db->select($getData);
$result        = [];
$currentParent = false;
$data          = null;
foreach ($execute as $row) {
    if ($currentParent != $row['id']) {
        if($data != null){ 
            $result[]= $data;
        }
        $data = [
            'name'     => $row['name'],
            'phone'    => $row['phone'],
            'category' => '',
            'address'  => ''
        ];
        $currentParent = $row['id'];
    }
    $data['category'] = empty($data['category']) ? $row['category'] : $data['category'] .", ". $row['category'];
    $data['address']  = empty($data['address'])  ? $row['address']  : $data['address']  .", ". $row['address'];
}
$result[]= $data;
echo json_encode($result);

现在的问题是，我将如何在我的结果中实现一组 5 个类别和一组 3 个地址.提前致谢.

Now the question is, How am I going to achieve 1 set of 5 category and 1 set of 3 address in my result. Thanks in advance.

TABLE_1 DATA
|------|------|-------|
|  ID  | NAME | PHONE |
|------|------|-------|
|  1   | JON  | 123   |
|------|------|-------|
TABLE_2 DATA
|------|------------|----------|
|  ID  | TABLE_1_ID | CATEGORY |
|------|------------|----------|
|  1   |      1     |   CAT1   |
|------|------------|----------|
|  2   |      1     |   CAT2   |
|------|------------|----------|
|  3   |      1     |   CAT3   |
|------|------------|----------|
|  4   |      1     |   CAT4   |
|------|------------|----------|
|  5   |      1     |   CAT5   |
|------|------------|----------|
TABLE_3 DATA
|------|------------|----------|
|  ID  | TABLE_1_ID | ADDRESS  |
|------|------------|----------|
|   1  |      1     |   ADR1   |
|------|------------|----------|
|   2  |      1     |   ADR2   |
|------|------------|----------|
|   3  |      1     |   ADR3   |
|------|------------|----------|

我使用 GROUP_CONCAT

SELECT a.id, a.name, a.phone, 
 GROUP_CONCAT(b.category ORDER BY b.category SEPARATOR ',') category,
 GROUP_CONCAT(c.address ORDER BY c.address SEPARATOR ',') address
    FROM table_1 a INNER JOIN table_2 b ON (a.id = b.table_1_id) INNER JOIN table_3 c ON (a.id = c.table_1_id)
    GROUP BY a.id, a.name, a.phone

这个查询也产生了相同的结果，我的 PHP 和上面一样.

This query also generating same result and my PHP is same as above.

推荐答案

您的查询涉及多个连接，这就是您为类别和地址获取多行的原因，在您的应用程序代码中，您可以通过调整一次显示类别和地址集您现有的逻辑.

Your query involves multiple joins that is why you are getting multiple rows for category and address, In your application code you can display set of category and address once by tweaking your existing logic.

为table_1的每一项维护category和address的临时变量($categories,$addresses)，并使用in_array()使确保没有重复的地址和类别添加到您的临时变量 $categories &$addresses 和内部循环，当您的迭代从一个项目更改为另一个项目时，然后创建一个逗号分隔的先前收集的类别和地址的字符串

Maintain temporary variables of category and address($categories,$addresses) for each item of table_1 and use in_array() to make sure no duplicate address and categories are added to your temporary variables $categories & $addresses and inside loop when your iteration changes from one item to different one then create a comma separated string of previously collected categories and addresses

$currentParent = false;
$data          = null;
$categories =$addresses = [];
foreach ($execute as $row) {
    if ($currentParent != $row['id']) {
        if($data != null){ 
            $data['category'] = implode (", ", $categories);
            $data['address'] = implode (", ", $addresses);
            $result[]= $data;
            $categories =$addresses = [];
        }
        $data = [
            'name'     => $row['name'],
            'phone'    => $row['phone'],
            'category' => '',
            'address'  => ''
        ];
        $currentParent = $row['id'];
    }
    if (in_array($row['category'], $categories) === false) {
        array_push($categories, $row['category']);
    }
    if (in_array($row['address'], $addresses) === false) {
        array_push($addresses, $row['address']);
    }
}

$data['category'] = implode (", ", $categories); // for last item
$data['address'] = implode (", ", $addresses); // for last item
$result[]= $data;
echo json_encode($result);

这篇关于如何从多个表的 SQL 查询中获取一组数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！