TP6一对多联查[查无数据]

鸡蛋灌饼QAQ

已于 2024-08-03 15:29:46 修改

阅读量668

点赞数 8

分类专栏： thinkphp6 mysql php 文章标签：数据库 php 链表

于 2024-07-30 22:50:56 首次发布

本文链接：https://blog.csdn.net/x7057385/article/details/140807893

版权

thinkphp6 同时被 3 个专栏收录

2 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

php

2 篇文章 0 订阅

订阅专栏

TP6一对多联查[查无数据]

场景
示例
构建SQL
翻译成TP方法
运行代码验证
示例数据SQL

场景

假设有一个订单表A表为主表，一个发货数据表B表，两张表是A表一对多B表的关系，但A表中存在无法与B表匹配的数据，B表中同样存在无法与A表中匹配的数据，两张表中都分别有两个月的数据，目的是查询，其中一个月，无法与另一张表匹配的数据集。

示例

这是主表A表

这是附表B表

A表中 sn 字段对应 B表中 sn_a 字段，一对多

想要的数据集，A表中2月份，无法与B表匹配( AB两表2月份中，B表有发货数据并且a表金额 > 0 )的数据集

示意图
图中把A和B表反过来看，红色看成 A表主表，蓝色看成B表附表

构建SQL

我们先把A表二月份数据全部查出，方便对比

SELECT a.* FROM pro_test_a a

WHERE a.month = 2

结果集如下

A表2月份数据
共有5条

分析，结果集是A表的数据，并且是不匹配的，所以需要用左联查LEFT JOIN
但这一步为了先展示两表匹配数据，先使用INNER JOIN

SELECT a.* FROM pro_test_a a

INNER JOIN pro_test_b b ON a.sn = b.sn_a

WHERE a.month = 2

A表2月份联查数据

现在需要限制的条件有两个，一个是联查A与B匹配的数据需要限制A表的price，一个是数据结果集需要限制月份，这两个条件限制，并不能同时写到WHERE下面，我们先进行精准匹配。将匹配的条件写进ON里面

SELECT a.* FROM pro_test_a a

INNER JOIN pro_test_b b ON a.sn = b.sn_a AND a.month = 2 AND b.month = 2 AND a.price > 0

WHERE a.month = 2

AB联查数据
此时可以看到，符合匹配的A表数据仅剩下一条，sn末尾024的数据，那么我们想要的结果集，就是最初出现的5条减去现在的一条，现在把INNER JOIN 换回LEFT JOIN 再进行where限制即可

SELECT a.* FROM pro_test_a a

LEFT JOIN pro_test_b b ON a.sn = b.sn_a AND a.month = 2 AND b.month = 2 AND a.price > 0

WHERE b.id IS NULL AND a.month = 2

最终结果集
现在SQL结果集符合预期，
重点：
①、需要匹配的限制条件，需要写进链表ON后面，本例ON后面的 a.month = 2 条件有没有它结果集一致，但实际中可能变更为其他条件，一定要写。
②、对结果集的限制写在WHERE后面。

翻译成TP方法

$test_a_model = new TestA();
$test_b_model = new TestB();
$b_table_name = Config::get('database.connections.mysql.prefix').
	$test_b_model->getName();
$list = $test_a_model
	->field('a.*')
	->alias('a')
	->leftJoin($b_table_name.' b','b.sn_a = a.sn AND a.month = 2 AND b.month = 2 AND a.price > 0')
	->where(function ($query){
		$query->whereNull('b.id');
	})
	->where('a.month','=',2)
	//->buildSql();
	->select()->toArray();

运行代码验证

SELECT `a`.* FROM `pro_test_a` `a` 

LEFT JOIN `pro_test_b` `b` ON `b`.`sn_a`=a.sn AND a.month = 2 AND b.month = 2 AND a.price > 0 

WHERE  (  `b`.`id` IS NULL )  AND `a`.`month` = '2'

结果集符合预期

大功告成

示例数据SQL

A表


CREATE TABLE `pro_test_a` (
  `id` int(11) NOT NULL,
  `sn` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `name` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `price` decimal(10,2) DEFAULT '0.00',
  `month` int(2) DEFAULT '0' COMMENT '月份',
  `time` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试A表';


INSERT INTO `pro_test_a` (`id`, `sn`, `name`, `price`, `month`, `time`) VALUES
(1, '2056244377033339380', '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐', '46.03', 1, 1706716791),
(2, '3764362392235604137', '面条一箱装鸡蛋面整箱挂面龙须面超细面条营养速食劲道袋包邮', '0.00', 1, 1706716794),
(3, '3764363652587254455', '宝贝无盐胡萝卜儿童面条营养无添加食盐早餐辅食营养超细挂面', '0.00', 2, 1706716929),
(4, '3764384532504399038', '北京鸡蛋面龙须面超细面条挂面早餐方便速食劲道炒面主食细面', '25.48', 2, 1706716720),
(5, '3764829746742312161', '宝贝儿童面条营养无添加食盐速食钙铁锌早餐无盐面条彩色挂面', '45.99', 1, 1706716601),
(6, '3764850914747412022', '宝贝儿童面条果蔬营养蔬菜面无添加盐龙须面超细挂面辅食无盐', '33.83', 1, 1706716348),
(7, '3764931843758397234', '宝贝儿童珍珠琪面无盐面片营养果蔬蔬菜面条彩琪面无添加食盐', '31.05', 2, 1706716247),
(8, '3764948331774106024', '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐', '47.74', 2, 1706715887),
(9, '3764952363482664149', '宝贝儿童面条营养无添加食盐速食钙铁锌早餐无盐面条彩色挂面', '0.00', 2, 1706715851);

ALTER TABLE `pro_test_a`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sn` (`sn`);

ALTER TABLE `pro_test_a`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

B表数据

CREATE TABLE `pro_test_b` (
  `id` int(11) NOT NULL,
  `sn_a` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `num` int(11) DEFAULT '0',
  `price` decimal(10,2) DEFAULT '0.00',
  `month` int(2) DEFAULT '0' COMMENT '月份',
  `time` int(10) UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `pro_test_b` (`id`, `sn_a`, `num`, `price`, `month`, `time`) VALUES
(1, '2056244377033339380', 1, '1.30', 1, 1706748841),
(2, '2056244377033339380', 2, '4.00', 1, 1706748841),
(3, '2056244377033339380', 2, '4.00', 1, 1706748841),
(4, '2056244377033339380', 2, '4.00', 1, 1706748841),
(5, '2056244377033339380', 2, '4.00', 1, 1706748841),
(6, '3764832411369540336', 1, '7.60', 1, 1706748839),
(7, '3764832411369540336', 1, '7.60', 1, 1706748839),
(8, '3764832411369540336', 1, '1.30', 1, 1706748839),
(9, '3764948331774106024', 1, '4.00', 2, 1706757178),
(10, '3764948331774106024', 1, '4.00', 2, 1706757178),
(11, '3764948331774106024', 1, '1.30', 2, 1706757178),
(12, '3764948331774106024', 4, '4.00', 2, 1706757178),
(13, '3764948331774106024', 1, '4.00', 2, 1706757178),
(14, '3764948331774106024', 1, '4.00', 2, 1706757178),
(15, '3764363652587254455', 1, '1.30', 2, 1706757168),
(16, '3764832411369540325', 2, '6.00', 2, 1706748825),
(17, '3764832411369540325', 2, '6.00', 2, 1706748825),
(18, '3764832411369540325', 2, '6.00', 2, 1706748825),
(19, '3764832411369540325', 2, '6.00', 2, 1706748825),
(20, '3764832411369540325', 2, '6.00', 2, 1706748825),
(21, '3764363652587254455', 2, '3.00', 2, 1706757168),
(22, '3764363652587254455', 2, '3.00', 2, 1706757168),
(23, '3764363652587254455', 2, '3.00', 2, 1706757168);

ALTER TABLE `pro_test_b`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `pro_test_b`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;