简单组合两个 SQL 表

Question

我需要帮助处理数据baze的一个输出。我有这两张表：

ANALYZE
    +----+------------+---------------------------------------+
    | ID | ID_analyze |                thread                 |
    +----+------------+---------------------------------------+
    |  4 |          1 | Failure of the power supply and media |
    |  5 |          2 | Failure of communication services     |
    +----+------------+---------------------------------------+
DATA
+----+------------+----------+
| id | ID_analyze |   norm   |
+----+------------+----------+
|  1 |          1 | A.11.2.1 |
|  2 |          1 | A.11.2.2 |
|  3 |          1 | A.12.3.1 |
|  4 |          2 | A.11.2.4 |
|  5 |          2 | A.13.1.1 |
+----+------------+----------+

在输出时，我需要得到这个：

+----+----------+
| ID |   norm   |
+----+----------+
|  4 | A.11.2.1 |
|    | A.11.2.2 |
|    | A.12.3.1 |
|  5 | A.11.2.4 |
|    | A.13.1.1 |
+----+----------+

不是这个：

我尝试了很多选择，但我总是写如下

    +----+----------+
    | ID |   norm   |
    +----+----------+
    |  4 | A.11.2.1 |
    |  4 | A.11.2.2 |
    |  4 | A.12.3.1 |
    |  5 | A.11.2.4 |
    |  5 | A.13.1.1 |
    +----+----------+

我试过了我的代码 ：

    SELECT  analyza.id 
    ra_data.norma
FROM ra_data
LEFT JOIN analyza
    ON ra_data.idh_analyza = analyza.idh_analyza
****************************************************
<table>
    <?php
        for($i = 0; $i < count($rows); $i++):
    ?>
        <tr>
             <td>
                <?php 
                    if($rows[$i]['id'] != $rows[$i-1]['id'])
                        echo $rows[$i]['id'];
                ?> 
            </td>
        </tr>
        <tr>
                <?php
                    echo $rows[$i]['norma'];
                ?>
        </tr>
    <?php
        endfor;
    ?>
</table>

比你们都寻求帮助。

Answer 1

这是一个使用带有完整演示的用户变量的解决方案。

.SQL：

--Data preparation for demo
create table `analyze`(id int, id_analyze int, thread varchar(200));
insert into `analyze` values
(4,1,''),
(5,2,'');
create table data (id int,id_analyze int, norm varchar(200));
insert into data values
(1,1,'A.11.2.1'),
(2,1,'A.11.2.2'),
(3,1,'A.12.3.1'),
(4,2,'A.11.2.4'),
(5,2,'A.13.1.1');
SELECT * FROM `analyze`;
SELECT * FROM data;
-- SQL needed
SET @last_id = 0;
SELECT 
    IF(d.display, a.id, '') ID,
    d.norm
FROM 
    (SELECT 
        id_analyze, 
        norm, 
        (id_analyze-@last_id) <> 0 display,
        @last_id:=id_analyze
     FROM data) d,
    `analyze` a
WHERE
    d.id_analyze = a.id_analyze;

输出：

mysql> SELECT * FROM `analyze`;
+------+------------+--------+
| id   | id_analyze | thread |
+------+------------+--------+
|    4 |          1 |        |
|    5 |          2 |        |
+------+------------+--------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM data;
+------+------------+----------+
| id   | id_analyze | norm     |
+------+------------+----------+
|    1 |          1 | A.11.2.1 |
|    2 |          1 | A.11.2.2 |
|    3 |          1 | A.12.3.1 |
|    4 |          2 | A.11.2.4 |
|    5 |          2 | A.13.1.1 |
+------+------------+----------+
5 rows in set (0.00 sec)
mysql> SET @last_id = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
    ->     IF(d.display, a.id, '') ID,
    ->     d.norm
    -> FROM
    ->     (SELECT
    ->         id_analyze,
    ->         norm,
    ->         (id_analyze-@last_id) <> 0 display,
    ->         @last_id:=id_analyze
    ->      FROM data) d,
    ->     `analyze` a
    -> WHERE
    ->     d.id_analyze = a.id_analyze;
+------+----------+
| ID   | norm     |
+------+----------+
| 4    | A.11.2.1 |
|      | A.11.2.2 |
|      | A.12.3.1 |
| 5    | A.11.2.4 |
|      | A.13.1.1 |
+------+----------+
5 rows in set (0.00 sec)

Answer 2

我会使用存储当前ID的标志

$lastID = "";
for($i = 0; i < count($rows); $i++):
    if($rows[i]['id']!=$lastID){
         $lastID = $rows[i]['id'];
         //the ID just changed, print it
         echo $rows[i]['id'];
    }
    echo $rows[i]['norma'];
endforeach;

没有经过测试，但逻辑是正确的。