本题目要求编写SQL语句,在SPJ
数据库中,查询至少用了s1
供应商所供应的全部零件的工程号jno
。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如
CREATE TABLE `j` ( -- 工程项目表
`jno` char(3) NOT NULL,-- 工程项目号
`jname` varchar(10) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
PRIMARY KEY (`jno`)
);
CREATE TABLE `p` ( -- 零件表
`pno` char(3) NOT NULL, -- 零件号
`pname` varchar(10) DEFAULT NULL,
`color` char(2) DEFAULT NULL,
`weight` smallint(6) DEFAULT NULL,
PRIMARY KEY (`pno`)
);
CREATE TABLE `s` ( -- 供应商表
`sno` char(3) NOT NULL, -- 供应商号
`sname` varchar(10) DEFAULT NULL,
`status` char(2) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sno`)
);
CREATE TABLE `spj` ( -- 零件供应表
`sno` char(3) NOT NULL,
`pno` char(3) NOT NULL,
`jno` char(3) NOT NULL,
`qty` smallint(6) DEFAULT NULL,
PRIMARY KEY (`sno`,`pno`,`jno`),
CONSTRAINT `fk_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`),
CONSTRAINT `fk_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`),
CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`)
);
表样例
请在这里给出上述表结构对应的表样例。例如
s
表:
p
表:
j
表:
spj
表:
输出样例:
请在这里给出输出样例。例如:
思路:
1. 首先,从spj表中选择jno字段。
2. 使用WHERE子句筛选出sno为's1'的记录,并且pno在子查询中的结果中存在。
3. 子查询中,从spj表中选择不重复的pno,并且sno为's1'。
4. 使用GROUP BY对jno进行分组。
5. 使用HAVING子句筛选出jno的计数等于子查询中a表的pno计数。
6. 子查询中,从spj表中选择pno,并且sno为's1',然后再次使用GROUP BY对pno进行分组,最后计算分组后的pno数量并将其命名为sum_pno。
7. 最终的查询结果将返回满足条件的jno值。
select jno from spj
where sno='s1'
and pno in(select distinct(pno) from spj
where sno='s1')
group by jno
having count(jno)=
(select count(a.pno) as sum_pno
from
(select pno from spj where sno='s1'group by pno) as a
)