mysql -- 多条件关联查询(2)

 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for qsyz_app_user
-- ----------------------------
DROP TABLE IF EXISTS `qsyz_app_user`;
CREATE TABLE `qsyz_app_user` (
  `userid` int(11) NOT NULL,
  `role` varchar(255) DEFAULT NULL COMMENT '办公室',
  `nickname` varchar(255) DEFAULT NULL COMMENT '名称'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of qsyz_app_user
-- ----------------------------
INSERT INTO `qsyz_app_user` VALUES ('1', '办公室', '张三');
INSERT INTO `qsyz_app_user` VALUES ('2', '办公室', '李四');
INSERT INTO `qsyz_app_user` VALUES ('3', '总经办', '张三');

DROP TABLE IF EXISTS `qsyz_app_version`;
CREATE TABLE `qsyz_app_version` (
  `id` int(11) NOT NULL,
  `version_name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `version_num` int(11) DEFAULT NULL,
  `version_size` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `version_text` varchar(255) DEFAULT NULL,
  `down_url` varchar(255) DEFAULT NULL,
  `phone_type` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of qsyz_app_version
-- ----------------------------
INSERT INTO `qsyz_app_version` VALUES ('440289813', '1.1.2', '20171205', '0', '2017-12-05 00:00:00', '56', 'http://192.168.1.101:8080/images/1.JPEG', 'android');
INSERT INTO `qsyz_app_version` VALUES ('440378169', '1.1.2', '20171109', '0', '2017-12-05 00:00:00', '565', 'http://192.168.1.101:8080/images/1.JPEG', 'android');
INSERT INTO `qsyz_app_version` VALUES ('749696543', '1.1.0', '1', '0', '2017-10-23 00:00:00', 'ghkfadjghkl', 'http://192.168.2.112:8080/images/app-debug.apk', 'android');
INSERT INTO `qsyz_app_version` VALUES ('750259190', '21', '23', '0', '2017-10-23 00:00:00', '家居空间', 'http://192.168.2.112:8080/images/ConstantUtil.java', 'android');

DROP TABLE IF EXISTS `qsyz_shop_order`;
CREATE TABLE `qsyz_shop_order` (
  `user_id` int(11) NOT NULL,
  `order_status` varchar(255) DEFAULT NULL COMMENT '支付成功',
  `create_date` datetime DEFAULT NULL COMMENT '日期要求为今天'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of qsyz_shop_order
-- ----------------------------
INSERT INTO `qsyz_shop_order` VALUES ('1', '支付成功', '2018-04-21 15:45:44');
INSERT INTO `qsyz_shop_order` VALUES ('2', '支付成功', '2018-04-21 15:45:49');
INSERT INTO `qsyz_shop_order` VALUES ('3', '支付成功', '2018-04-21 15:45:55');

 

问题（一）如下：

查询出今天办公室人员的订餐人员

qsyz_app_user用户表:
userid、role、 nickname

qsyz_shop_order订单表:
user_id、order_status、 create_date

role 为“办公室”
order_status 为“支付成功”
日期要求为今天

实现语句：

SELECT
    *
FROM
    qsyz_app_user a,
    qsyz_shop_order b
WHERE
    a.userid = b.user_id
AND b.order_status = '支付成功'
AND b.create_date > '2017-12-25 16:05:05';

#按日期模糊查询出结果 
SELECT
    a.nickname
FROM
    qsyz_app_user a,
    qsyz_shop_order b
WHERE
    a.userid = b.user_id
AND a.role = '办公室'
AND b.order_status = '支付成功'
AND b.create_date LIKE '%2018-04-21%';

mysql like的写法：

MYSQL如何进行sql like (sql查询结果)的查询 

create_date like CONCAT('%','2018-04-21','%');  #可以避免sql注入。
create_date like '%2018-04-21%';

select * from table1 where `text` like (select name from table2 where id =3);
select * from table1 where `text` like '%'(select name from table2 where id =3)'%';
select * from table1 where `text` like '%' + (select name from table2 where id =3) +'%';
select * from table1 where `text` like '%(select name from table2 where id =3)%';
select * from table1 where `text` like '%’select name from table2 where id =3‘%';

select * from table1 where `text` like '%(
select name from table2 where id =3
) +'%';（第二条SQL单独占一行）

 

问题（二）如下：

SELECT
    *
FROM
    qsyz_app_version
WHERE
    version_num = (
        SELECT
            max(version_num)
        FROM
            qsyz_app_version
        WHERE
            phone_type = 'android'
    );