时间戳

首页 > 解决方案 > MySQL JOIN：仅在第一次出现时打印左表行

mysql - MySQL JOIN：仅在第一次出现时打印左表行

问题描述

有两个表：

    first                 second
+----+------+    +----+----------+-------+
| id | name |    | id | first_id | value |
+----+------+    +----+----------+-------+
|  1 |  one |    |  1 |        1 |    10 |
|  2 |  two |    |  2 |        1 |    11 |
+----+------+    |  3 |        2 |    20 |
                 |  4 |        2 |    22 |
                 +----+----------+-------+

我有的：

SQL查询

SELECT T1.`id` AS firstId,
       T1.`name` AS name,
       T2.`value` AS value
FROM `first` AS T1
JOIN `second` AS T2
  ON T2.`first_id` = T1.`id`
ORDER BY T1.`id` ASC,
         T2.`id` ASC;

结果

+---------+------+-------+
| firstId | name | value |
+---------+------+-------+
|       1 |  one |    10 |
|       1 |  one |    11 |
|       2 |  two |    20 |
|       2 |  two |    22 |
+---------+------+-------+

我想得到什么：

如果该条目在前一行中，我想省略左表条目的列值。

结果

+---------+------+-------+
| firstId | name | value |
+---------+------+-------+
|       1 |  one |    10 |
|         |      |    11 |
|       2 |  two |    20 |
|         |      |    22 |
+---------+------+-------+

标签： mysqlsqljoin

解决方案

这是我的努力：

SELECT firstId, name, value FROM
  (SELECT CASE
           WHEN @prev != T1.id THEN @first := 1
           ELSE @first := 0
         END,
         CASE WHEN @first = 1 THEN T1.id ELSE "" END AS firstId,
         CASE WHEN @first = 1 THEN T1.name ELSE "" END AS name,
         T2.value AS value,
         @prev := T1.id
  FROM first AS T1
  INNER JOIN second AS T2
    ON T2.first_id = T1.id
  INNER JOIN (SELECT @prev:=0)f
  ORDER BY T1.id ASC,
           T2.id ASC
   )T

sqlfiddle

更新了表格乱序时的答案（我们必须先排序，然后才能加入变量）。

SELECT firstId, name, value FROM
  (SELECT @first := CASE
           WHEN @prev != T1id THEN 1
           ELSE 0
         END,
         CASE WHEN @first = 1 THEN T1id ELSE "" END AS firstId,
         CASE WHEN @first = 1 THEN T1name ELSE "" END AS name,
         T2value AS value,
         @prev := T2first_Id
  FROM (SELECT T1.id as T1id, T2.first_Id as T2first_Id, T1.name as T1name, T2.value as T2value FROM 
        first AS T1
  INNER JOIN second AS T2
    ON T2.first_id = T1.id ORDER BY T1.id ASC,
           T2.id ASC)ORD,(SELECT @prev:=-1)f

   )T

sqlfiddle 用于何时表出现故障 或者如果您希望删除第一部分以确定@first。

SELECT firstId, name, value FROM
  (SELECT CASE WHEN @prev != T1id THEN T1id ELSE "" END AS firstId,
          CASE WHEN @prev != T1id THEN T1name ELSE "" END AS name,
         T2value AS value,
         @prev := T2first_Id
  FROM (SELECT T1.id as T1id, T2.first_Id as T2first_Id, T1.name as T1name, T2.value as T2value FROM 
        first AS T1
  INNER JOIN second AS T2
    ON T2.first_id = T1.id ORDER BY T1.id ASC,
           T2.id ASC)ORD,(SELECT @prev:=-1)f

   )T

推荐阅读