一个字段同时满足多个条件的查询

来源：互联网发布：身份证被盗用网络贷款编辑：程序博客网时间：2024/05/13 22:19

构造一个如下图所示的表：

№_001: 创建表 tb_a

DROP TABLE tb_a CASCADE CONSTRAINT;COMMIT;CREATE TABLE tb_a(model_name varchar2(10),parts_name varchar2(15));COMMIT;

№_002: 导入数据

INSERT INTO tb_a (model_name, parts_name) VALUES ('飞机','轮胎');INSERT INTO tb_a (model_name, parts_name) VALUES ('飞机','座椅');INSERT INTO tb_a (model_name, parts_name) VALUES ('飞机','机翼');INSERT INTO tb_a (model_name, parts_name) VALUES ('飞机','玻璃');INSERT INTO tb_a (model_name, parts_name) VALUES ('汽车','轮胎');INSERT INTO tb_a (model_name, parts_name) VALUES ('汽车','座椅');INSERT INTO tb_a (model_name, parts_name) VALUES ('汽车','玻璃');INSERT INTO tb_a (model_name, parts_name) VALUES ('自行车','轮胎');INSERT INTO tb_a (model_name, parts_name) VALUES ('自行车','座椅');INSERT INTO tb_a (model_name, parts_name) VALUES ('自行车','方向盘');COMMIT;

查询要求：

查询含有“轮胎”、“座椅”、“玻璃” 三个配件的设备

SELECT x.model_nameFROM tb_a xWHERE x.parts_name IN ('轮胎','座椅','玻璃') --限制三个配件GROUP BY x.model_nameHAVING COUNT(*)=3 --关键语句

0 0