mysql - 左连接不返回 mysql 中左表中的所有行

Question

我真的没有发现为什么left join不返回t1的所有行。请提供任何帮助。

t1 架构:

 CREATE TABLE `tbl_assigned` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `Round` int(2) NOT NULL,
     `TraineeID` varchar(7) NOT NULL,
     `Name` varchar(25) NOT NULL,
     `Mobile` int(10) DEFAULT NULL,
     `BatchID` varchar(35) NOT NULL,
     `Remarks` varchar(8) DEFAULT NULL,
     `District` varchar(15) DEFAULT NULL,
     `Comments` varchar(21) DEFAULT NULL,
     `Level` varchar(2) DEFAULT NULL,
     `Trade` int(1) DEFAULT NULL,
     `Status` int(1) DEFAULT NULL,
     `Photo` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`ID`),
     KEY `TraineeID` (`TraineeID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=356 DEFAULT CHARSET=utf8

t2 架构:

CREATE TABLE `tbl_attn_temp` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `TraineeID` varchar(7) NOT NULL,
 `ScannerID` int(7) NOT NULL,
 `attnDate` date NOT NULL,
 `attnTime` time NOT NULL,
 `Status` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2135 DEFAULT CHARSET=latin1

查询:

SELECT t1.TraineeID, t2.attnDate
FROM tbl_assigned t1
LEFT JOIN tbl_attn_temp t2 on t1.TraineeID=t2.TraineeID
WHERE t1.BatchID='ID-Welding/SSTS-01M/R7/01' and t2.attnDate='2015-12-28'

输出:

TID   attnDate
15950 2015-12-28
24310 2015-12-28
24317 2015-12-28
24327 2015-12-28
24400 2015-12-28
24973 2015-12-28
25186 2015-12-28
25281 2015-12-28
25285 2015-12-28
25300 2015-12-28

但 t1 表有 15 个符合此条件的 TraineeID。

SELECT t1.TraineeID FROM tbl_assigned t1 WHERE t1.BatchID='ID-Welding/SSTS-01M/R7/01'

输出:

15950
20012
21173
24310
24317
24327
24400
24936
24973
25033
25186
25281
25282
25285
25300

SELECT t2.TraineeID, t2.attnDate FROM tbl_attn_temp t2 WHERE t2.attnDate='2015-12-28'

输出:

ID        Date
SSTS001 2015-12-28
SSTS001 2015-12-28
SSTS001 2015-12-28
15950 2015-12-28
24317 2015-12-28
24738 2015-12-28
25186 2015-12-28
25281 2015-12-28
24973 2015-12-28
24310 2015-12-28
24400 2015-12-28
24327 2015-12-28
25300 2015-12-28
25285 2015-12-28
SSTS002 2015-12-28
28702 2015-12-28
28702 2015-12-28
22934 2015-12-28
26620 2015-12-28
24068 2015-12-28
21343 2015-12-28
1151467 2015-12-28
24931 2015-12-28
24931 2015-12-28
24931 2015-12-28
4872 2015-12-28
24071 2015-12-28
24786 2015-12-28
6203 2015-12-28
24069 2015-12-28

Answer 1

检查BatchID字段是否有空格问题。空格可能在缺失记录的开头和结尾。

还要检查其他记录的日期。

解决记录开始和结束可能存在的空间问题

使用这个查询。

SELECT t1.TraineeID, t2.attnDate
FROM tbl_assigned t1
LEFT JOIN tbl_attn_temp t2 on t1.TraineeID=t2.TraineeID
WHERE ifnull(trim(t1.BatchID),'')='ID-Welding/SSTS-01M/R7/01'

如果您得到预期的记录，那么还要检查其他记录中的日期是否为“2015-12-28”。