mysql语句

问题:

test表结构如图

需求是按照name分组，且过滤出status必须包含1,2的数据

得到的结果如图，flag只是个标记位，只要能通过此字段知道此用户是包含1,2的就行了

大佬们有没有好的思路

回答1:

SELECT
NAME,
IF(LOCATE(1, GROUP_CONCAT(STATUS)) != 0 AND LOCATE(2, GROUP_CONCAT(STATUS)) != 0,"true","false")
FROM
test
GROUP BY
NAME

回答2:

select name,flag=count(1) from #tt where status in (1,2) group by name

这样的结果就会没有flag=0的字段
或者这样就有0的数据

select name,flag=(select count(1) from #tt where name=a.name and status in (1,2)) from #tt a group by name

回答3:

SELECT NAME, COUNT(1) AS flag
FROM test
WHERE STATUS IN(1,2)
GROUP BY NAME
HAVING COUNT(1)=2

回答4:

select DISTINCT name, '1' as flag from test where status = 1 or status = 2