mysql每日一题0721 关联查询

😄题目如下

测试数据

CREATE TABLE T0721 
(
DOCNUM INT,
STATUS VARCHAR(26)
);
INSERT INTO  T0721 VALUES (33,'FULL');
INSERT INTO  T0721 VALUES (33,'NOFULL');
INSERT INTO  T0721 VALUES (34,'FULL');
INSERT INTO  T0721 VALUES (35,'FULL');
INSERT INTO  T0721 VALUES (35,'NOFULL');
INSERT INTO  T0721 VALUES (36,'FULL');
INSERT INTO  T0721 VALUES (37,'FULL');
INSERT INTO  T0721 VALUES (38,'FULL');
INSERT INTO  T0721 VALUES (38,'NOFULL')

参考答案

• solution1

SELECT
	a.docnum 
FROM
	t0721 a
	LEFT JOIN ( SELECT docnum, count( DISTINCT STATUS ) status_num FROM t0721 GROUP BY 1 ) b
	 ON a.DOCNUM = b.docnum 
WHERE
	status_num = 1 
	AND a.STATUS IN ('FULL')

• solution2

select a.docnum
from t0721 a
group by 1
having group_concat(status)='FULL'