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

loading...


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 . 如果有人知道如何做到这一点会很棒 . :)

loading...

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]>
    
评论

暂时没有评论!