mysql每日一题0721 关联查询

😄题目如下
image

测试数据


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'

posted on 2021-07-21 10:21  多一点  阅读(55)  评论(0)  编辑  收藏  举报

导航