当前位置：首页 > news >正文

ThinkPHP A表和B表一对多关联，根据B表中符合条件记录的某个字段的值对A表数据进行排序。

news 来源：原创 2024/9/19 14:18:35

A表是简历表结构如下

CREATE TABLE `fa_resume` (`user_id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL COMMENT '姓名',`sex` enum('1','0') DEFAULT '1' COMMENT '性别:1=男,0=女',`birthday` date DEFAULT NULL COMMENT '出生年月',`shenfen` enum('1','2') DEFAULT '1' COMMENT '身份:1=职场人,2=学生',`qiuzhi_status` enum('1','2','3','4') DEFAULT '1' COMMENT '求职状态:1=离职-随时到岗,2=在职-月内到岗,3=在职-考虑机会,4=在职-暂不考虑',`xueli` enum('1','2','3','4','5','6','7','8') DEFAULT '1' COMMENT '学历:1=初中及一下,2=中专/技校,3=高中,4=大专,5=本科,6=硕士,7=博士,8=博士后',`gerenyoushi` text COMMENT '个人优势',`ziwojieshao` text COMMENT '自我介绍',`status` enum('1','2') DEFAULT '1' COMMENT '状态:1=显示,2=隐藏',`updatetime` bigint(11) DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='个人简历';

B表是VIP订单表结构如下

CREATE TABLE `fa_vip_order` (`id` int(11) NOT NULL AUTO_INCREMENT,`order_num` varchar(255) DEFAULT NULL COMMENT '订单号',`user_id` int(11) DEFAULT NULL COMMENT '用户',`sale_id` int(11) DEFAULT NULL COMMENT '套餐id',`name` varchar(255) DEFAULT NULL COMMENT '套餐名称',`price` decimal(10,2) DEFAULT NULL COMMENT '金额',`days` int(11) DEFAULT NULL COMMENT '增加天数',`call_num` int(11) DEFAULT NULL COMMENT '电话总次数',`residue_call_num` int(11) DEFAULT NULL COMMENT '剩余电话次数',`pay_type` enum('wechat','alipay') DEFAULT NULL COMMENT '支付方式:wechat=微信,alipay=支付宝',`pay_order_num` varchar(255) DEFAULT NULL COMMENT '支付单号',`pay_time` bigint(11) DEFAULT NULL COMMENT '支付时间',`status` enum('1','2') DEFAULT '1' COMMENT '状态:1=待支付,2=已支付',`expire_time` bigint(11) DEFAULT NULL COMMENT '到期时间',`createtime` bigint(11) DEFAULT NULL,`updatetime` bigint(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='工人VIP订单';

需求是开通会员的用户简历排到前面，且开通时间越早，排的越靠前。

代码如下

/*** 查询是否有会员，有会员返回会员开通时间，无费用默认9999999999* @param $user_id* @return string*/
function getPayTime(){return "(SELECT COALESCE((SELECT pay_timeFROM fa_vip_orderWHERE  status = '2'AND expire_time > UNIX_TIMESTAMP(NOW())AND r.user_id = user_idORDER BY pay_time ASCLIMIT 1),9999999999) AS order_weigh) as order_weigh";
}//简历列表方法$lists = Resume::alias('r')->join('user u', 'u.id = r.user_id')->where('r.status', '1')->where($where)->where('r.user_id', 'in', $user_ids)->field(array('r.*','u.nickname','u.avatar','u.mobile',getPayTime()))->order('order_weigh asc')->paginate();

SQL分析

( SELECT `r`.*, `u`.`nickname`, `u`.`avatar`, `u`.`mobile`,(SELECT COALESCE((SELECT pay_timeFROM fa_vip_orderWHERE  status = '2'AND expire_time > UNIX_TIMESTAMP(NOW())AND r.user_id = user_idORDER BY pay_time ASCLIMIT 1),9999999999) AS order_weigh) as order_weigh
FROM `fa_resume` `r`
INNER JOIN `fa_user` `u` ON `u`.`id` = `r`.`user_id`
ORDER BY `order_weigh` ASC )

逐行解释
外层查询：
( SELECT ... )：这是一个外层查询，用于获取 fa_resume 表中的记录，并进行排序。
选择列：
SELECT r.*, u.nickname, u.avatar, u.mobile：从 fa_resume 表 (r) 中选择所有列，以及 fa_user 表 (u) 中的 nickname, avatar, 和 mobile` 列。
内层子查询：
(SELECT COALESCE(...))：这是一个内层子查询，用于计算每个 user_id 的 pay_time 的最小值，并为不存在的 pay_time 赋予一个默认值 9999999999。
COALESCE(...)：如果第一个参数为空，则返回第二个参数。这里用于防止 pay_time 不存在的情况。
内层的 SELECT pay_time 子查询用于找到每个 user_id 的符合条件的 pay_time 的最小值。
WHERE status = '2': 仅考虑 status 为 2 的记录。
AND expire_time > UNIX_TIMESTAMP(NOW()): 仅考虑 expire_time 大于当前时间的记录。
AND r.user_id = user_id: 仅考虑与 fa_resume 表中的 user_id 匹配的记录。
ORDER BY pay_time ASC: 按照 pay_time 升序排列。
LIMIT 1: 仅选择第一个结果（即 pay_time 最小的记录）。
表联结：
FROM fa_resume rINNER JOINfa_user uONu.id=r.user_id：联结 fa_resume 表 (r) 和 fa_user 表 (u)，基于 fa_user的id和fa_resume的user_id` 的匹配关系。
排序：
ORDER BY order_weigh ASC: 根据 order_weigh 字段对结果进行升序排序。
总结
这条 SQL 查询语句的主要目的是根据 fa_vip_order 表中的 pay_time 字段来对 fa_resume 表中的记录进行排序。它通过内层子查询找到每个 user_id 的 pay_time 最小值，并在外层查询中将这些值与 fa_resume 表的记录进行联结，最后根据这些 pay_time 的最小值进行排序。如果某个 user_id 没有符合条件的 pay_time 记录，则使用默认值 9999999999 来替代。