首页 文章

与前一行比较并保留为空，如果相同的值

提问于
浏览 次
0

我现在有以下问题 .

我有一张桌子，在那里我得到了名字，比如巡回赛，我有一个与旅游相关的超过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，因此使用相同的tourname在最后一行再次打印tourname . 如果有人知道如何做到这一点会很棒 . :)

mysql

1 回答

  • 0

    你可以使用子查询来完成它 . 这将按正确的顺序对结果进行排序，以便像这样使用 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;

    Sample

    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]>
    回复于

相关问题