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

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

img

img

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');

简单暴力的解法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

img

用外连接实现，left outer join或right outer join.

如果你不需要的话，做聚合处理呢

条件聚合操作应该可以满足你的需求，参考使用下相关。

根据已采纳大哥的代码改，我这边具体表结构就不出来了，主要就是一对一对一然后对多，然后发现变量被替代之后还是会出现重复数据的问题所以对表就行一个父级id的排序，这样变量就不会因为被替代再次出现重复

select
if(flaga='0',0,tabl.invoiceid) invoiceid
FROM(
select invoiceid,
if(@stra=concat(ah.houseid,ab.billid),'0','1') flaga,
@stra:=concat(ah.houseid,ab.billid) stra
FROM (select @stra:='') tmp,app_house ah
LEFT JOIN app_house_charge ahc on ahc.houseid=ah.houseid
LEFT JOIN par_property pp on pp.propertyId=ah.heatnature
LEFT JOIN app_bill ab on ab.houseid=ah.houseid 
LEFT JOIN app_bill_offer abo on abo.offerid=ab.offerid and abo.houseid=ah.houseid
LEFT JOIN par_warmperiod pw on pw.warmperiodid=abo.warmperiodid
LEFT JOIN app_contract ac on ac.houseid=ah.houseid
left join (select * from app_invoice where status=2  ORDER BY billid ASC)ai on ai.billid=ab.billid
where  ah.delstate=0 and ac.delstate=0 and ai.status=2
)tabl