SQL连表查询如何实现部分重复数据为空

Question

在多个表连表查询时可能会出现重复的数据，如何把重复的数据显示为null值。 表usert作为所有表的关系表，表usera和表userb为连接表。表usert重复则不需要为null，但usera或者userb有一个表数据比另外一个表数据多则另外一个表会别拉伸出多一行的数据，希望这一行的数据为null

CREATE TABLE `usera`  (
  `u_id` int(11) NOT NULL,
  `u_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `u_test` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `t_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`u_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `usera` VALUES (1, 'A1', '达瓦达瓦', 1);
INSERT INTO `usera` VALUES (2, 'A2', '达瓦若', 2);
INSERT INTO `usera` VALUES (3, 'A3', '王大伟', 3);
INSERT INTO `usera` VALUES (4, 'A4', '王大伟多', 3);

CREATE TABLE `userb`  (
  `b_id` int(11) NOT NULL,
  `b_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `b_test` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `t_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`b_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;


INSERT INTO `userb` VALUES (1, 'B1', 'or供热供热', 3);
INSERT INTO `userb` VALUES (2, 'B2', '瓦特丰东股份', 2);
INSERT INTO `userb` VALUES (3, 'B3', 'wa哇哈哈哈', 1);


CREATE TABLE `usert`  (
  `t_id` int(11) NOT NULL,
  `t_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `t_test` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `usert` VALUES (1, 'T1', 'rrrr');
INSERT INTO `usert` VALUES (2, 'T2', 'erwr');
INSERT INTO `usert` VALUES (3, 'T3', '599');

Answer 1

简单暴力的解法1：

select tt.t_id,
tt.t_name,
tt.t_test,
if(flagb='0',null,b_t_id) b_t_id,
if(flagb='0',null,b_id) b_id,
if(flagb='0',null,b_name) b_name,
if(flagb='0',null,b_test) b_test,
if(flaga='0',null,a_t_id) a_t_id,
if(flaga='0',null,a_id) a_id,
if(flaga='0',null,a_name) a_name,
if(flaga='0',null,a_test) a_test 
from (
select t.t_id,
t.t_name,
t.t_test,
a.t_id a_t_id,
a.u_id a_id,
a.u_name a_name,
a.u_test a_test,
b.t_id b_t_id,
b.b_id b_id,
b.b_name b_name,
b.b_test b_test,
if(@stra=concat(t.t_id,a.u_id),'0','1') flaga,
if(@strb=concat(t.t_id,b.b_id),'0','1') flagb,
@stra:=concat(t.t_id,a.u_id) stra,
@strb:=concat(t.t_id,b.b_id) strb
from (select @stra:='',@strb:='') tmp,usert t 
left join usera a  on a.t_id=t.t_id  
left join userb b on b.t_id=t.t_id
) tt
order by tt.t_id