我现在有三张表，希望将这三个表组合起来查询，然后按照更新时间排序

Question

有三张表code_task、task_status、task_version，其中code_task和task_version均有有更新时间，我希望按照这两张表中的更新时间进行排序

我现在使用如下语句查询

<sql id="Base_Column_List">
 ct.id, ct.title, content, tags, ct.status, ct.status as taskStatus,
    ts.explanation as taskExplanation, type, difficulty,
    valid_period_begin as validPeriodBegin, valid_period_end as validPeriodEnd,
    specify_participate_users as specifyParticipateUsers,
    participate_count as participateCount, commend_count as commendCount,
    concern_count as concernCount, browse_count as browseCount, discuss_count as discussCount,
    task_cover as taskCover, ct.create_date as createDate, ct.update_date as updateDate
</sql>

<select id="selectByCheck" resultType="cn.spring.inter.entity.CodeTask">
 SELECT
    <include refid="Base_Column_List" />,
    tv.id as versionID,
    tv.status AS versionStatus,
    (SELECT explanation from task_status where id = tv.id) as versionExplanation
    FROM code_task ct
    left join task_version tv on ct.id = tv.taskID
    left join task_status ts on ct.status = ts.id
    <choose>
 <when test="sort=='asc'">
 ORDER BY ct.update_date DESC limit #{start}, #{dataLength}
        </when>
 <otherwise> ORDER BY ct.update_date DESC limit #{start}, #{dataLength}
        </otherwise>
 </choose></select>

建表的语句

DROP TABLE IF EXISTS `code_task`;
CREATE TABLE `code_task` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务ID', `title` varchar(100) NOT NULL COMMENT '标题', `userID` int(11) NOT NULL COMMENT '任务创建人', `content` varchar(10000) DEFAULT NULL COMMENT '任务要求', `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `tags` varchar(50) DEFAULT NULL COMMENT '标签', `status` varchar(10) DEFAULT NULL COMMENT '任务状态', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8 COMMENT='任务';

DROP TABLE IF EXISTS `task_status`;
CREATE TABLE `task_status` (
 `id` int(11) NOT NULL COMMENT '任务状态ID', `explanation` varchar(30) NOT NULL COMMENT '任务状态说明', PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='任务状态';
SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `task_version`;
CREATE TABLE `task_version` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务ID', `version` varchar(100) NOT NULL COMMENT '版本号', `userID` int(11) NOT NULL COMMENT '版本创建人', `taskID` int(11) NOT NULL COMMENT '版本所属任务ID', `code` varchar(10000) DEFAULT NULL COMMENT '版本执行代码', `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `test_result` varchar(1000) DEFAULT NULL COMMENT '版本测试结果', `remark` varchar(1000) DEFAULT NULL COMMENT '版本备注内容', `status` varchar(10) DEFAULT NULL COMMENT '版本状态', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='任务提交版本';