- 在工作时出现这么一个需求,一个人对应了另一张表中的多条记录,但是我要根据这多条记录的某一字段来进行查询
- 人员基本信息
人员的其他信息
我需要根据populationType中的属性来查询
最开始使用SELECT * from (SELECT DISTINCT p.id ,(SELECT GROUP_CONCAT(DISTINCT populationType)
from person_info pi where p.id = pi.personId and pi.status = 1 and populationType is not null ) as c from person p
) cd WHERE cd.c is not null
结果查询中既有1,又有2的,但是有2的不符合需求,优化之后的sql
SELECT * from (SELECT DISTINCT p.id ,(SELECT GROUP_CONCAT(DISTINCT populationType)
from person_info pi where p.id = pi.personId and pi.status = 1 and populationType is not null ) as c from person p
) cd WHERE INSTR(cd.c,1) and !INSTR(cd.c,2)
查询出来的结果就没有2的属性了