mysql - 与上一行比较，如果值相同，则留空

Question

我目前遇到以下问题。

我有一个表，我从其中获得名称，例如旅游，并且我有一个子表，其中超过 1 行与旅游相关，即表旅程。

我想要得到什么:

+---+------------+-------------+-------------+
|   |  Name      |   Subname   |   Info Sub  |
+---+------------+-------------+-------------+
| 1 | Tour 1     | Journey 1   | duration,km |
| 2 |            | Journey 2   | duration,km |
| 3 |            | Journey 3   | duration,km |
| 4 | Tour 2     | Journey 1   | duration,km |
| 5 |            | Journey 2   | duration,km |
| 6 | Tour 3     | Journey 1   | duration,km |
+---+------------+-------------+-------------+

我已经尝试过:

SELECT 
if (t.id = @tid,"",(SELECT tourname FROM tours WHERE id = @tid)),
tj.journeyname,
if(@tid != t.id, @tid := t.id,"")
FROM tours t 
JOIN tours_journeys tj ON tj.toursid = t.id ORDER BY t.id

似乎 @tid 在选择完成之前被分配，因此巡回赛名称会以相同的巡回赛名称再次打印在最后一行。如果有人知道如何做到这一点，那就太好了。 :)

Answer 1

你可以用子查询来做到这一点。这按正确的顺序对结果进行排序，以使用 IF 如下所示:

SELECT  
    IF(result.id = @myg, '', @myg:= result.id) AS gname,
    result.*
FROM (
    SELECT 
        t.id,
        tj.journeyName
    FROM tours t
    JOIN tour_journeys tj ON tj.tour_id = t.id
    CROSS JOIN ( SELECT   @myg:='') AS init
    ORDER BY t.id, tj.id
) result;

示例

MariaDB [test]> SELECT
    ->     IF(result.id = @myg, '', @myg:= result.id) AS gname,
    ->     result.*
    -> FROM (
    ->     SELECT
    ->         t.id,
    ->         tj.journeyName
    ->     FROM tours t
    ->     JOIN tour_journeys tj ON tj.tour_id = t.id
    ->     CROSS JOIN ( SELECT   @myg:='') AS init
    ->     ORDER BY t.id, tj.id
    -> ) result;
+-------+------+-------------+
| gname | id   | journeyName |
+-------+------+-------------+
| 1     |    1 | journey 1   |
|       |    1 | journey 2   |
|       |    1 | journey 3   |
|       |    1 | journey 4   |
|       |    1 | journey 5   |
|       |    1 | journey 6   |
| 2     |    2 | journey 1   |
|       |    2 | journey 2   |
+-------+------+-------------+
8 rows in set (0.006 sec)

MariaDB [test]>