mysql中的left join查询为所有结果返回空行

zf2sa74q  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(529)

我在左连接查询中遇到了一个奇怪的问题：
表1：费率

  1. Serial, peopleSerial, jobSerial
  2. 1, 23, 1
  3. 1, 24, 1
  4. 1, 25, 1
  5. 1, 26, 1

表2：人员

  1. Serial, otherKey, name1, address, etc...
  2. 23, 65, John, street, etc...
  3. 24, 66, Mike, street, etc...
  4. 25, 67, Alex, street, etc...

从people表左连接数据应该相当简单。以下是我使用的查询：

  1. SELECT rates.*, people.* FROM rates
  2. LEFT JOIN people ON people.Serial = Rates.peopleSerial

我希望看到这样的结果：预期结果

  1. Serial, peopleSerial, jobSerial, Serial, otherKey, name1, address, etc...
  2. 1, 23, 1, 23, 65, John, Street, etc...
  3. 2, 24, 1, 24, 66, Mike, Street, etc...
  4. 3, 25, 1, 25, 67, Alex, Street, etc...
  5. 4, 26, 1, NULL, NULL, NULL, NULL, etc...

但这是我实际得到的结果：实际结果

  1. Serial, peopleSerial, jobSerial, Serial, otherKey, name1, address, etc...<br>
  2. 1, 23, 1, NULL, NULL, NULL, NULL, etc...
  3. 2, 24, 1, NULL, NULL, NULL, NULL, etc...
  4. 3, 25, 1, NULL, NULL, NULL, NULL, etc...
  5. 4, 26, 1, NULL, NULL, NULL, NULL, etc...

编辑：
这里有一个数据转储和相同的sql fiddle，它返回相同的结果。上面的例子是这个的简化版本。我已经清除了subco\u people\u dev表中的大部分内容，因为它是机密信息，但仍应显示一些填充字段：

  1. CREATE TABLE IF NOT EXISTS `subcon_rates` (
  2.   `serial` int(11) NOT NULL AUTO_INCREMENT,
  3.   `subcon_serial` int(11) DEFAULT NULL,
  4.   `job_serial` int(11) DEFAULT NULL,
  5.   `type_serial` int(11) DEFAULT NULL,
  6.   `rate` decimal(10,2) DEFAULT NULL,
  7.   `chargeRate` decimal(10,2) DEFAULT NULL,
  8.   `period` date DEFAULT NULL,
  9.   PRIMARY KEY (`serial`)
  10. ) ENGINE=MyISAM AUTO_INCREMENT=999 DEFAULT CHARSET=latin1;
  12. INSERT INTO `subcon_rates` (`serial`, `subcon_serial`, `job_serial`, `type_serial`, `rate`, `chargeRate`, `period`) VALUES
  13.     (134, 52, 27, 1, 10.00, 15.00, '2018-10-14'),
  14.     (138, 56, 27, 1, 25.00, 30.00, '2018-10-14'),
  15.     (136, 55, 27, 1, 20.00, 25.00, '2018-10-14'),
  16.     (139, 54, 27, 1, 35.00, 40.00, '2018-10-14'),
  17.     (140, 52, 27, 1, 10.00, 15.00, '2018-10-07'),
  18.     (141, 56, 27, 1, 25.00, 30.00, '2018-10-07'),
  19.     (142, 55, 27, 1, 20.00, 25.00, '2018-10-07'),
  20.     (143, 54, 27, 1, 35.00, 40.00, '2018-10-07'),
  21.     (153, 54, 27, 1, 10.00, 15.00, '2018-10-21'),
  22.     (152, 55, 27, 1, 10.00, 15.00, '2018-10-21'),
  23.     (866, 52, 27, 1, 10.00, 15.00, '2018-10-21'),
  24.     (150, 52, 27, 1, 10.00, 15.00, '2018-10-21');
  26. CREATE TABLE IF NOT EXISTS `subcon_people_dev` (
  27.   `serial` int(11) NOT NULL AUTO_INCREMENT,
  28.   `subconNumber` varchar(4) DEFAULT NULL,
  29.   `type` text,
  30.   `title` text,
  31.   `name1` text,
  32.   `surname` text,
  33.   `nino` text,
  34.   `ref` text,
  35.   `tradingname` text,
  36.   `crn` text,
  37.   `pName` text,
  38.   `pUTR` text,
  39.   `add1` text,
  40.   `add2` text,
  41.   `add3` text,
  42.   `add4` text,
  43.   `postcode` text,
  44.   `UTR` text,
  45.   `role` text,
  46.   `company` text,
  47.   `insuranceExpire` date DEFAULT NULL,
  48.   `approved` int(11) NOT NULL DEFAULT '0',
  49.   `sortCode` text,
  50.   `accountNumber` text,
  51.   `accountName` text,
  52.   `placcount` text,
  53.   PRIMARY KEY (`serial`),
  54.   UNIQUE KEY `subconNumber` (`subconNumber`)
  55. ) ENGINE=MyISAM AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;
  57. INSERT INTO `subcon_people_dev` (`serial`, `subconNumber`, `type`, `title`, `name1`, `surname`, `nino`, `ref`, `tradingname`, `crn`, `pName`, `pUTR`, `add1`, `add2`, `add3`, `add4`, `postcode`, `UTR`, `role`, `company`, `insuranceExpire`, `approved`, `sortCode`, `accountNumber`, `accountName`, `placcount`) VALUES
  58.     (1, '0001', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  59.     (2, '0010', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  60.     (3, '0011', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  61.     (4, '0012', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  62.     (5, '0013', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  63.     (6, '0014', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  64.     (7, '0015', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  65.     (8, '0016', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  66.     (9, '0017', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  67.     (10, '0018', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  68.     (11, '0019', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  69.     (12, '0002', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  70.     (13, '0020', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  71.     (14, '0021', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  72.     (15, '0022', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  73.     (16, '0023', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  74.     (17, '0024', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  75.     (18, '0025', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  76.     (19, '0026', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  77.     (20, '0027', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  78.     (21, '0028', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  79.     (22, '0029', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  80.     (23, '0003', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  81.     (24, '0030', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  82.     (25, '0031', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  83.     (26, '0032', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  84.     (27, '0033', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  85.     (28, '0034', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  86.     (29, '0035', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  87.     (30, '0036', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  88.     (31, '0037', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  89.     (32, '0038', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  90.     (33, '0039', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  91.     (34, '0004', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  92.     (35, '0040', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  93.     (36, '0041', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  94.     (37, '0042', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  95.     (38, '0043', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  96.     (39, '0044', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  97.     (40, '0045', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  98.     (41, '0046', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  99.     (42, '0047', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  100.     (43, '0048', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  101.     (44, '0049', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  102.     (45, '0005', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  103.     (46, '0050', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  104.     (47, '0051', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  105.     (48, '0052', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  106.     (49, '0006', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  107.     (50, '0007', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  108.     (51, '0008', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  109.     (59, '62', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  110.     (60, '54', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  111.     (61, '55', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  112.     (62, '59', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  113.     (63, '53', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
  114.     (64, '58', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  115.     (65, '56', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  116.     (66, '60', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  117.     (67, '0061', '1', '', '', '', '', '', '', '', NULL, NULL, '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
  118.     (68, '0063', '1', '', '', '', '', '', '', '', NULL, NULL, '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', '');

https://www.db-fiddle.com/f/ubwys5uvzy62fjiwjhpwpd/1

mysql

2条答案

按热度按时间
bkhjykvo

bkhjykvo1#

你需要改变你的加入顺序。万一 Left Join ，最左边的表应该是要考虑所有行的表（无论其他表中是否有匹配的行）。

  1. select subcon_rates.*, subcon_people_dev.* 
  2. from subcon_people_dev
  3. left join subcon_rates on subcon_people_dev.`serial` = subcon_rates.subcon_serial
赞(0）分享  回复(0）举报  2021-06-18
mzillmmw

mzillmmw2#

因为没有匹配的密钥，所以有空值。。。在subco\u people\u dev中，没有subco\u serial（52、54、55、56）的行。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com