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_time
FROM fa_vip_order
WHERE status = '2'
AND expire_time > UNIX_TIMESTAMP(NOW())
AND r.user_id = user_id
ORDER BY pay_time ASC
LIMIT 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_time
FROM fa_vip_order
WHERE status = '2'
AND expire_time > UNIX_TIMESTAMP(NOW())
AND r.user_id = user_id
ORDER BY pay_time ASC
LIMIT 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 来替代。