【MySQL】查询是否连续5次失败

场所房间表

场所编号和房间号联合唯一，一对多关系

场所编号关联商家ID，一对一关系

CREATE TABLE `aisw_room` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PLACE_CODE` varchar(20) DEFAULT NULL COMMENT '场所编号',
  `ROOM_NO` varchar(10) DEFAULT NULL COMMENT '房间号',
  `SSID` varchar(60) DEFAULT NULL COMMENT 'ssid',
  `SSID_PWD` varchar(60) DEFAULT NULL COMMENT 'WiFi密码',
  `ACCOUNT` varchar(50) DEFAULT NULL COMMENT '宽带账号',
  `LONGITUDE` varchar(10) DEFAULT NULL COMMENT '经度',
  `LATITUDE` varchar(10) DEFAULT NULL COMMENT '纬度',
  `PLACETYPEGATEWAY` varchar(10) DEFAULT NULL COMMENT '产品类型',
  `UPDATETIME` datetime DEFAULT NULL COMMENT '更新时间',
  `CREATETIME` datetime DEFAULT NULL COMMENT '创建时间',
  `TIMELINE` datetime DEFAULT NULL COMMENT '时间戳',
  `DEVICEMAC` varchar(32) DEFAULT NULL COMMENT '设备mac',
  `STATUS` tinyint(1) DEFAULT NULL COMMENT '状态(0-无效 1-有效)',
  `DEVICE_CODE` varchar(50) DEFAULT NULL COMMENT '设备编号(管综平台使用)',
  `GZ_ROOM_NO` varchar(10) DEFAULT NULL COMMENT '管综场所名称',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IDX_ROOM_ROOMNO_CODE` (`ROOM_NO`,`PLACE_CODE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2317 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='场所房间信息表';

扫码记录表

场所编号在这里不存值，按商家ID和房间号关联上面的房间表
是否连接成功根据 ISCONNECTION状态筛选

CREATE TABLE `aisw_e_service_scan_history_msg` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MERCHANT_ID` int(11) DEFAULT NULL COMMENT '商家ID',
  `SERVICE_TYPE` varchar(50) DEFAULT NULL COMMENT '服务类型(与字典表AISW_MERCHANT_DICT表关联)',
  `USER_ID` int(11) DEFAULT NULL COMMENT '用户ID(与AISW_USER表关联)',
  `WEIXINID` varchar(100) DEFAULT NULL COMMENT '微信ID',
  `PHONENUM` varchar(20) DEFAULT NULL COMMENT '手机号',
  `PLACECODE` varchar(20) DEFAULT NULL COMMENT '场所编号',
  `ROOMNO` varchar(10) DEFAULT NULL COMMENT '房间号',
  `ACCEPT_STATUS` tinyint(1) DEFAULT '0' COMMENT '受理状态(0-未受理 1-已受理)',
  `CREATETIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATETIME` datetime DEFAULT NULL COMMENT '更新时间',
  `TIMELINE` datetime DEFAULT NULL COMMENT '时间戳',
  `LONGITUDE` varchar(10) DEFAULT NULL COMMENT '经度',
  `LATITUDE` varchar(10) DEFAULT NULL COMMENT '纬度',
  `ACTIVETYPE` tinyint(1) DEFAULT NULL COMMENT '获取方式：1.扫码；2搜索公众号(字段已废弃)',
  `ISCONNECTION` tinyint(1) NOT NULL COMMENT '是否连接WiFi：0.未连接；1.连接',
  `LOCALATITUDE` varchar(20) DEFAULT NULL COMMENT '当前扫码维度',
  `LOCALONGITUDE` varchar(20) DEFAULT NULL COMMENT '当前扫码经度',
  `ISSENTAREA` tinyint(1) NOT NULL COMMENT '数据是否对区域发送过：0.未发送；1.成功返回；-1失败返回',
  `ISSENTCITY` tinyint(1) NOT NULL COMMENT '数据是否对市级发送过：0.未发送；1.成功返回；-1失败返回',
  `CONNECT_ERR_MSG` varchar(50) DEFAULT NULL COMMENT 'WIFI连接失败错误信息描述',
  `CREATE_BY` varchar(50) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IDX_TE_SCAN_HISTORY_PHONENUM` (`PHONENUM`) USING BTREE,
  KEY `IDX_TE_SCAN_HISTORY_WXID` (`WEIXINID`) USING BTREE,
  KEY `IDX_E_S_SCAN_M_ID` (`MERCHANT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='连接wifi服务通知表';

我是想按扫码记录的创建时间倒序排序，筛选对应的房间的前五条记录，

如果都为未连接的状态，说明符合连续5次扫码失败的记录

所以这是最开始的SQL

SELECT *
FROM aisw_e_service_scan_history_msg 
WHERE roomno = room.ROOM_NO AND merchant_id = merchant.ID<br>ORDER BY CREATETIME DESC<br>LIMIT 5

然后为了只获取【是，否】的状态，又做了一层子查询

对上面的结果集按连接状态进行分组，如果COUNT函数计数连接失败的个数为5，则SELECT 1,否则为0

SELECT IF( COUNT(ISCONNECTION = 0) = 5, 1, 0)
FROM (
    SELECT *
    FROM aisw_e_service_scan_history_msg 
    WHERE roomno = 701 AND merchant_id = 4
    ORDER BY CREATETIME DESC
    LIMIT 5
) AS recent
GROUP BY ISCONNECTION;
+----------------------------------------+
| IF( COUNT(ISCONNECTION = 0) = 5, 1, 0) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.05 sec)

我想直接把这个子查询放入主查询里面，作为一个字段子查询

但是发现这样嵌套了之后不能把主查询的表字段作为条件提供给里面嵌套的查询

执行结果会报错，说不知道条件来自哪里

SELECT
    room.ID,
    merchant.ID AS MERCHANT_ID, 
    (
        SELECT IF( COUNT(ISCONNECTION = 0) = 5, 1, 0)
        FROM (
            SELECT *
            FROM aisw_e_service_scan_history_msg 
            WHERE roomno = room.ROOM_NO AND merchant_id = merchant.ID
            ORDER BY CREATETIME DESC
            LIMIT 5
        ) AS recent
        GROUP BY ISCONNECTION
    ) AS record,
    room.PLACE_CODE,
    room.ROOM_NO,
    room.SSID,
    room.SSID_PWD,
    room.ACCOUNT,
    room.LONGITUDE,
    room.LATITUDE,
    room.PLACETYPEGATEWAY,
    room.UPDATETIME,
    room.CREATETIME,
    room.TIMELINE,
    room.DEVICEMAC,
    room.STATUS,
    room.DEVICE_CODE,
    room.GZ_ROOM_NO 
FROM
    aisw_room AS room
    JOIN aisw_merchant AS merchant ON room.PLACE_CODE = merchant.PLACE_CODE
WHERE
    room.PLACE_CODE = 36212521169073 
    AND room.STATUS = 1
ORDER BY
    CREATETIME DESC
> 1054 - Unknown column 'room.ROOM_NO' in 'where clause'
> 时间: 0s

然后纠结了很久，就想到用GROUP_CONCAT函数来实现

对这个房间的扫码记录进行分组拼接，合并每个记录的连接状态

SELECT GROUP_CONCAT(ISCONNECTION)
FROM aisw_e_service_scan_history_msg 
WHERE roomno = 701 AND merchant_id = 4

如果要做 JOIN查询，可以先按商家ID和房号分组

SELECT merchant_id, roomno, GROUP_CONCAT(ISCONNECTION)
FROM aisw_e_service_scan_history_msg 
GROUP BY merchant_id, roomno

把最新创建的记录放前面排序，这样就能看到前五次的情况

SELECT GROUP_CONCAT(ISCONNECTION ORDER BY CREATETIME DESC)
FROM aisw_e_service_scan_history_msg 
WHERE roomno = 701 AND merchant_id = 4;
+---------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(ISCONNECTION ORDER BY CREATETIME DESC)                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------+
| 0,0,0,0,0,0,1,1,0,1,1,1,1,1,1,1,1,0,0,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0 |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

然后使用LEFT截取字符加上 IF字符匹配判断，就能获取需要的结果了

SELECT
    merchant_id, 
    roomno, 
    IF('0,0,0,0,0' = LEFT( GROUP_CONCAT(ISCONNECTION), 9), 1, 0) AS res
FROM aisw_e_service_scan_history_msg 
GROUP BY merchant_id, roomno;
+-------------+--------+-----+
| merchant_id | roomno | res |
+-------------+--------+-----+
|           1 | 102    |   0 |
|           3 | 120    |   0 |
|           4 | 701    |   1 |
|           4 | 705    |   0 |
+-------------+--------+-----+
4 rows in set (0.04 sec)

我暂时没用JOIN来写，用字段子查询来完成

下面还附加了一个【最近一个月扫码记录的查询】

SELECT
    room.ID,
    merchant.ID AS MERCHANT_ID, 
    (
        SELECT IF ('0,0,0,0,0' = LEFT(GROUP_CONCAT(ISCONNECTION ORDER BY CREATETIME DESC), 9), 1, 0) AS res
        FROM aisw_e_service_scan_history_msg 
        WHERE roomno = room.ROOM_NO AND merchant_id = merchant.ID
    ) AS res,
    (
        SELECT COUNT(1) AS record
        FROM aisw_e_service_scan_history_msg 
        WHERE roomno = room.ROOM_NO AND merchant_id = merchant.ID AND CREATETIME >= DATE_SUB( NOW(), INTERVAL 1 MONTH)
    ) AS record,
    room.PLACE_CODE,
    room.ROOM_NO,
    room.SSID,
    room.SSID_PWD,
    room.ACCOUNT,
    room.LONGITUDE,
    room.LATITUDE,
    room.PLACETYPEGATEWAY,
    room.UPDATETIME,
    room.CREATETIME,
    room.TIMELINE,
    room.DEVICEMAC,
    room.STATUS,
    room.DEVICE_CODE,
    room.GZ_ROOM_NO 
FROM
    aisw_room AS room
    JOIN aisw_merchant AS merchant ON room.PLACE_CODE = merchant.PLACE_CODE
WHERE
    room.PLACE_CODE = 36212521169073 
    AND room.STATUS = 1
ORDER BY
    CREATETIME DESC