【tp6一对多查询，结果集显示主表信息加附表行数】【tp6 join 闭包一个临时查询结果】

场景

存在一个主表A，一个附表B，主表一对多附表，但主表中含有附表中没有对应的数据，同样，附表中含有主表中没有对应的数据，现需要查询的结果集，是主表和附表完全对应的数据，展示主表信息，以及每一行附表的匹配行数，并且查询需要以B表的某一个字段作为where条件。

样例

这是主表A表
这是附表B表

A表中 sn 字段对应 B表中 sn_a 字段

想要的结果集：

sn	name	price	b_num
2056244377033339380	宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐	46.03	5
3764948331774106024	宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐	47.74	6

编写SQL

1、使用子查询确认 inner join 链表后的数据集作为条件，以消除【主表中含有附表中没有对应的数据】和【附表中含有主表中没有对应的数据】

SELECT sn_a FROM pro_test_a a 

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

WHERE b.time >=  1706748841 and  b.time <= 1706757178

这就得到了一对多状态下的所有 sn

2、联查条件中包含以上子查询，得到

SELECT a.sn,a.name,a.price

FROM pro_test_a a

WHERE a.sn IN (

   SELECT sn_a FROM pro_test_a a 

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

	WHERE b.time >=  1706748841 and  b.time <= 1706757178
    
)

此时结果集，仅差 b_num 没有出现，需要一个临时表，与A表联查

3、创建临时表，结果集出现 b_num

SELECT a.sn,a.name,a.price,c.b_num

FROM pro_test_a a

INNER JOIN 

	( SELECT a.sn,COUNT(*) as b_num 

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

	WHERE b.time >=  1706748841 and  b.time <= 1706757178 

	GROUP BY a.sn ) 

	as c ON a.sn = c.sn
	
WHERE a.sn IN (

    SELECT sn_a FROM pro_test_a a 

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

	WHERE b.time >=  1706748841 and  b.time <= 1706757178
)

翻译成TP方法

//$test_a_model 是A表的实例化模型
$c_temp = $test_a_model
	->field('a.sn,COUNT(*) as b_num')
	->alias('a')
	->join('pro_test_b b','a.sn = b.sn_a')
	->where([
		['b.time','>=',1706748841],
		['b.time','<=',1706757178]
	])
	->group('a.sn')
	->buildSql();
$list = $test_a_model->field('a.sn,a.name,a.price,c.b_num')
	->alias('a')
	->join([$c_temp=>'c'],'a.sn = c.sn')
	->where(function($query) use ($test_a_model){
		$sn_list = $test_a_model
			->field('b.sn_a')
			->alias('a')
			->join('pro_test_b b','a.sn = b.sn_a')
			->where([
				['b.time','>=',1706748841],
				['b.time','<=',1706757178]
			])
			->group('a.sn')
			->select()->toArray();
			$sn_list = array_column($sn_list,'sn_a');
			$query->whereIn('a.sn',$sn_list);
	})
//	->buildSql()
	->select()
	->toArray();

运行代码验证

//buildSql后结果是
( SELECT `a`.`sn`,`a`.`name`,`a`.`price`,`c`.`b_num` 

FROM `pro_test_a` `a` 

INNER JOIN ( 

	SELECT a.sn,COUNT(*) as b_num 

	FROM `pro_test_a` `a` 

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

	WHERE  `b`.`time` >= '1706748841'  

	AND `b`.`time` <= '1706757178' 

	GROUP BY `a`.`sn` 
	
) `c` ON `a`.`sn`=`c`.`sn` 

WHERE  (  `a`.`sn` IN ('2056244377033339380','3764948331774106024') ) )
//下面的where闭包解析后直接出结果集了

//查询结果集
array (size=2)
  0 => 
    array (size=4)
      'sn' => string '2056244377033339380' (length=19)
      'name' => string '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐' (length=84)
      'price' => string '46.03' (length=5)
      'b_num' => int 5
  1 => 
    array (size=4)
      'sn' => string '3764948331774106024' (length=19)
      'name' => string '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐' (length=84)
      'price' => string '47.74' (length=5)
      'b_num' => int 6

!!!大功告成

测试数据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',
  `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`, `time`) VALUES
(1, '2056244377033339380', '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐', '46.03', 1706716791),
(2, '3764362392235604137', '面条一箱装鸡蛋面整箱挂面龙须面超细面条营养速食劲道袋包邮', '0.00', 1706716794),
(3, '3764363652587254455', '宝贝无盐胡萝卜儿童面条营养无添加食盐早餐辅食营养超细挂面', '0.00', 1706716929),
(4, '3764384532504399038', '北京鸡蛋面龙须面超细面条挂面早餐方便速食劲道炒面主食细面', '25.48', 1706716720),
(5, '3764829746742312161', '宝贝儿童面条营养无添加食盐速食钙铁锌早餐无盐面条彩色挂面', '45.99', 1706716601),
(6, '3764850914747412022', '宝贝儿童面条果蔬营养蔬菜面无添加盐龙须面超细挂面辅食无盐', '33.83', 1706716348),
(7, '3764931843758397234', '宝贝儿童珍珠琪面无盐面片营养果蔬蔬菜面条彩琪面无添加食盐', '31.05', 1706716247),
(8, '3764948331774106024', '宝贝儿童蝴蝶面蔬菜钙铁锌胡萝卜鸡蛋彩蝶面片辅食无添加食盐', '47.74', 1706715887),
(9, '3764952363482664149', '宝贝儿童面条营养无添加食盐速食钙铁锌早餐无盐面条彩色挂面', '0.00', 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',
  `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`, `time`) VALUES
(1, '2056244377033339380', 1, '1.30', 1706748841),
(2, '2056244377033339380', 2, '4.00', 1706748841),
(3, '2056244377033339380', 2, '4.00', 1706748841),
(4, '2056244377033339380', 2, '4.00', 1706748841),
(5, '2056244377033339380', 2, '4.00', 1706748841),
(6, '3764832411369540336', 1, '7.60', 1706748839),
(7, '3764832411369540336', 1, '7.60', 1706748839),
(8, '3764832411369540336', 1, '1.30', 1706748839),
(9, '3764948331774106024', 1, '4.00', 1706757178),
(10, '3764948331774106024', 1, '4.00', 1706757178),
(11, '3764948331774106024', 1, '1.30', 1706757178),
(12, '3764948331774106024', 4, '4.00', 1706757178),
(13, '3764948331774106024', 1, '4.00', 1706757178),
(14, '3764948331774106024', 1, '4.00', 1706757178);

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;