从3个表中选择不同列的公共记录

Question

我有以下3个重复的id表我想从相同的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;