查询组中包含特定类型不包含特定类型的数据

 

/*
Navicat MySQL Data Transfer

Source Server         : x
Source Server Version : x
Source Host           : x
Source Database       : x

Target Server Type    : MYSQL
Target Server Version : 50505
File Encoding         : 65001

Date: 2016-05-12 15:19:45
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for treatment_log
-- ----------------------------
DROP TABLE IF EXISTS `treatment_log`;
CREATE TABLE `treatment_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='医患表';

-- ----------------------------
-- Records of treatment_log
-- ----------------------------
INSERT INTO `treatment_log` VALUES ('1', '1', '1', '1');
INSERT INTO `treatment_log` VALUES ('2', '1', '1', '2');
INSERT INTO `treatment_log` VALUES ('3', '1', '1', '3');
INSERT INTO `treatment_log` VALUES ('4', '2', '2', '1');
INSERT INTO `treatment_log` VALUES ('5', '2', '2', '5');
INSERT INTO `treatment_log` VALUES ('6', '3', '3', '1');

 根据template_id 做过滤 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

SELECT
    *
FROM
    (
        SELECT
            CONCAT(user_id, '_', doctor_id) u_d,
            GROUP_CONCAT(template_id) str
        FROM
            treatment_log
        GROUP BY
            user_id,
            doctor_id
    ) t

 

1.查询包含5 但不包含2 的分组数据

SELECT
    *
FROM
    (
        SELECT
            CONCAT(user_id, '_', doctor_id) u_d,
            GROUP_CONCAT(template_id) str
        FROM
            treatment_log
        GROUP BY
            user_id,
            doctor_id
    ) t
WHERE
    LOCATE('2', t.str) = 0
AND LOCATE('5', t.str) > 0

结果:

2.查询只包含1的分组数据

where t.str = '1'

结果：