mysql - 从 3 个表中选择具有不同列的 Common Record

Question

我有以下 3 个具有重复 ID 的表。我想从所有 3 个表中检索具有相同 ID 但具有不同名称和日期的记录。我需要查询以获得预期结果输出。

CREATE TABLE Student1
    (`id` int,`status` int,`amount` int , `Name` varchar(10), `date` varchar(55))
;
    INSERT INTO Student1
    (`id`,`status`,`amount`, `Name`, `date`)
VALUES
    (1,0,4500, 'ram', '04/02/2012'),
    (2,0,2000, 'shyam', '05/09/2013'),

    (4,0,1500, 'ghanshyam', '08/11/2014')
;


CREATE TABLE Student2
    (`id` int,`status` int,`amount` int , `Name` varchar(10), `date` varchar(55))
;

INSERT INTO Student2
    (`id`,`status`,`amount`, `Name`, `date`)
VALUES

    (3,0,4500, 'gopal', '04/02/2012'),
    (2,0,8000, 'radheshyam', '15/11/2013'),
    (4,1,1500, 'ghanshyam', '18/10/2015')
;

CREATE TABLE Student3
    (`id` int,`status` int,`amount` int , `Name` varchar(10), `date` varchar(55))
;

INSERT INTO Student3
    (`id`,`status`,`amount`, `Name`, `date`)
VALUES

    (1,1,4500, 'ram', '14/02/2012'),
    (2,0,6500, 'radhe', '11/11/2014'),
    (3,1,4500, 'gopal', '14/02/2015')
;

异常结果:

   id status    amount     Name        date
   2    0       2000      shyam      05/09/2013
   2    0       6500      radhe      11/11/2014
   2    0       8000    radheshyam   15/11/2013

Answer 1

您只需使用union all 将表组合在一起。一种方法是:

select s.*
from (select s.* from student1 s union all
      select s.* from student2 s union all
      select s.* from student3 s
     ) s
where id = 2;

不过，正如我在评论中所说，通常您会拥有三个表而不是一个表。

我意识到我可能误解了这个问题。如果您想查找具有相同 ID 但名称不同的记录，请使用:

select s.id, group_concat(s.name) as names
from (select s.* from student1 s union all
      select s.* from student2 s union all
      select s.* from student3 s
     ) s
group by s.id
having count(distinct name) = 3 -- or perhaps >= 2, depending on what you mean

如果你想要完整的记录，你可以将它连接回原始表。

编辑:

如果你想要所有的原始行:

select s.*
from (select s.id, group_concat(s.name) as names
      from (select s.* from student1 s union all
            select s.* from student2 s union all
            select s.* from student3 s
           ) s
      group by s.id
      having count(distinct name) = 3 
     ) ss join
     (select s.* from student1 s union all
      select s.* from student2 s union all
      select s.* from student3 s
     ) s
     on ss.id = s.id;