mysql - 如果 ID 重复，则显示数据并更改值

Question

我有带表的数据库..

A
idA | name
-----------------
1   | A

B
idB | idA | name
-----------------------
2   | 1   | AA
3   | 1   | BB
4   | 1   | CC

C
idC | idA | name
-----------------------
6   | 1   | AAA
7   | 1   | BBB

查询是..

select
   A.*,
   B.idB, B.name,
   C.idC, C.name
from
   A
join
   B on B.idA=A.idA
join
   C on C.idA=A.idA

那么结果是..

idA  | name    | idB  | name  | idC  | name
----------------------------------------------------
1    | A       | 2    | AA    | 6    | AAA
1    | A       | 2    | AA    | 7    | BBB
1    | A       | 3    | BB    | 6    | AAA
1    | A       | 3    | BB    | 7    | BBB
1    | A       | 4    | CC    | 6    | AAA
1    | A       | 4    | CC    | 7    | BBB

I need result like this..

idA  | name    | idB  | name  | idC  | name
----------------------------------------------------
1    | A       | 2    | AA    | 6    | AAA
     |         | 3    | BB    | 7    | BBB
     |         | 4    | CC    |      | 
     |         |      |       |      | 
     |         |      |       |      | 
     |         |      |       |      | 

每个相同或重复的值/id，将被更改/替换为 null 或空白值..

有人能帮忙吗？

Answer 1

要解决这个问题，你需要一个表之间的“连接”键。实际上，解决方案(在 MySQL 中)不会使用 join，但您需要为每个指定行号:

select (case when rn = 1 then ida end) as ida,
       (case when rn = 1 then namea end) as namea,
       max(idb) as idb, max(nameb) as nameb,
       max(idc) as idc, max(namec) as namec
from ((select a.idA, a.name as namea, b.id as idb, b.name as nameb,
              NULL as idc, NULL as namec,
              (@rnb := @rnb + 1) as rn
       from a join
            b
            on b.ida = a.id
      ) union all
      (select a.idA, a.name, NULL, NULL,
              c.id as idc, c.name as namec,
              (@rnc := @rnc + 1) as rn
       from a join
            c
            on c.idc = a.id
      )
     ) abc
group by ida, namea, rn;

union all/group by 本质上是实现一个 full outer join(在本例中)，MySQL 不支持。