先创建两个表
-- 1.用户表(这个表示作为子查询的表)
DROP TABLE IF EXISTS `loginuser`;
CREATE TABLE `loginuser` (
`weight` double DEFAULT NULL COMMENT '体重',
`height` double DEFAULT NULL COMMENT '身高',
`user_id` varchar(40) NOT NULL COMMENT '主键id',
`user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
`password` varchar(100) DEFAULT NULL COMMENT '用户密码',
`gender` int(4) DEFAULT NULL COMMENT '用户性别',
`age` int(5) DEFAULT NULL COMMENT '用户年龄',
`intrest` varchar(255) DEFAULT NULL COMMENT '用户兴趣爱好',
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `key_pra_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- 插入数据
-- ----------------------------
INSERT INTO `loginuser` VALUES ('32', '23', '11', '辅导费', '32', '0', '32', '23人', '23');
INSERT INTO `loginuser` VALUES ('856865', '1143', '121212121', '11', '1', '1', '18343', '1212343', '18902002322');
这个表示主表:
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`testid` varchar(40) NOT NULL COMMENT '主键',
`address` varchar(40) DEFAULT NULL COMMENT '地址',
`reason` varchar(40) DEFAULT NULL COMMENT '预约原因',
`linkway` varchar(40) DEFAULT NULL COMMENT '联系方式',
`bookingtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '预约时间',
`assigntime` timestamp NULL DEFAULT NULL COMMENT '派单时间',
`handledept` varchar(40) DEFAULT NULL COMMENT '处理部门',
`state` varchar(40) DEFAULT NULL COMMENT '状态',
`userid` varchar(40) DEFAULT NULL COMMENT '提出问题人id',
`acceptperson` varchar(40) DEFAULT NULL COMMENT '接收人',
`delayedreson` varchar(40) DEFAULT NULL COMMENT '延迟原因',
`returnreson` varchar(40) DEFAULT NULL COMMENT '回退原因',
PRIMARY KEY (`testid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- 初始化数据
-- ----------------------------
INSERT INTO `test` VALUES ('1', '111', '不热', null, '2016-11-01 15:04:51', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('10', '000', '不热', null, '2016-11-01 15:04:51', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('2', '222', '不热', null, '2016-11-01 15:04:52', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('3', '333', '不热', null, '2016-11-01 15:04:52', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('4', '444', '不热', null, '2016-11-01 15:04:53', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('5', '555', '不热', null, '2016-11-01 15:04:53', null, null, null, '121212121', null, null, null);
INSERT INTO `test` VALUES ('6', '666', '不热', null, '2016-11-01 15:04:55', null, null, null, '11', null, null, null);
INSERT INTO `test` VALUES ('7', '777', '不热', null, '2016-11-01 15:04:59', null, null, null, '11', null, null, null);
INSERT INTO `test` VALUES ('8', '888', '不热', null, '2016-11-01 15:04:59', null, null, null, '11', null, null, null);
INSERT INTO `test` VALUES ('9', '999', '不热', null, '2016-11-01 15:05:00', null, null, null, '11', null, null, null);
test表中有一个字段userid对应的是loginuser表中的user_id 根据user_id查询出loginuser表中的user_name
select t.* ,(select user_name from loginuser logu where logu.user_id=t.userid )from test t
有任何疑问 请咨询qq群:539741306 或qq:1151143484