MySQL-多表左连接查询

三张表的数据

/*Table structure for table `tc` */

DROP TABLE IF EXISTS `tc`;

CREATE TABLE `tc` (
  `id` varchar(255) NOT NULL,
  `time` int(4) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `tc` */

insert  into `tc`(`id`,`time`,`text`)
 values ('001',1,'wsl好事可爱'),
 ('002',2,'汪苏泷唱歌很好听'),
 ('003',3,'汪苏泷真帅'),
 ('004',1,'wsl小可爱'),
 ('005',1,'哈哈哈');

/*Table structure for table `tcr` */

DROP TABLE IF EXISTS `tcr`;

CREATE TABLE `tcr` (
  `id` int(4) NOT NULL,
  `trends_content_id` varchar(255) DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `isread` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `tcr` */

insert  into `tcr`(`id`,`trends_content_id`,`user_id`,`isread`) 
values (1,'001','u1','是'),
(2,'002','u1','否'),
(3,'001','u1','是'),
(4,'003','u1','否'),
(5,'002','u2','是');

/*Table structure for table `tcu` */

DROP TABLE IF EXISTS `tcu`;

CREATE TABLE `tcu` (
  `id` varchar(255) NOT NULL,
  `ontop` int(4) DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `trends_content_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `tcu`(`id`,`ontop`,`user_id`,`trends_content_id`)
 values ('1',0,'u1','002'),
 ('2',1,'u1','001'),
 ('3',1,'u1','003'),
 ('4',1,'u2','003'),
 ('5',1,'u2','001'),
 ('6',1,'u1','004');

去重和加上条件

SELECT t1.id,t1.text,t2.ontop,t3.isread,t2.user_id FROM(

	SELECT * FROM tc
	) AS t1
	
	LEFT JOIN 
	(
	SELECT * FROM tcu WHERE tcu.`user_id` = 'u1' GROUP BY tcu.`trends_content_id`
	) AS t2

	ON t1.id = t2.trends_content_id -- AND t2.ontop != 0)

	LEFT JOIN 
	(SELECT * FROM tcr WHERE tcr.`user_id` = 'u1' GROUP BY tcr.`trends_content_id`
	) AS t3
	
	ON t1.id = t3.trends_content_id
	
	 WHERE t1.`time`<3 
		AND t1.text LIKE '%汪苏泷%' -- 谓语查询条件
		OR t2.ontop=1
		
	ORDER BY t2.ontop DESC

代替方法

-- 成功
SELECT tc.`id`,tc.`text`,tc.`time`,tcu.`ontop`,tcr.`isread`,tcu.`user_id` FROM tc

LEFT JOIN tcu ON (tc.`id`=tcu.`trends_content_id` AND tcu.user_id = 'u1')
LEFT JOIN tcr ON (tc.`id`=tcr.`trends_content_id` AND tcu.user_id = 'u1')
 WHERE tc.`time`<3 AND tc.`text` LIKE '%汪苏泷%' OR tcu.`ontop`=1 -- 查询条件，一定要or条件！！！

GROUP BY tc.`id`
ORDER BY tcu.`ontop` DESC

返回的数据