USE SPJ;
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
CITY CHAR(20),
PRIMARY KEY (SNO));
CREATE TABLE P (PNO CHAR(4) NOT NULL,
PNAME CHAR(20) NOT NULL,
COLOR CHAR(8),
WEIGHT SMALLINT,
PRIMARY KEY(PNO));
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
PRIMARY KEY(JNO));
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
QTY SMALLINT,
PRIMARY KEY (SNO,PNO,JNO),
CONSTRAINT `spj_ibfk_1` FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `spj_ibfk_2` FOREIGN KEY (PNO) REFERENCES P(PNO) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `spj_ibfk_3` FOREIGN KEY (JNO) REFERENCES J(JNO) ON DELETE CASCADE ON UPDATE CASCADE);上面是我构建表的时候写的代码
表的内容如下查询的题目是------->>>>>找出供应工程J1零件为红色的供应商号码SNO我的查询代码是USE SPJ;
SELECT DISTINCT SNO FROM SPJ
WHERE JNO='J1' AND PNO IN
(SELECT PNO
FROM P
WHERE COLOR='红');
但是一直都查不到东西,但是如果我用
USE SPJ;
SELECT DISTINCT * FROM SPJ
WHERE JNO = 'J1' AND PNO IN ('P1', 'P4' , 'P6')
就可以成功查询,请求各位大佬指教
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
CITY CHAR(20),
PRIMARY KEY (SNO));
CREATE TABLE P (PNO CHAR(4) NOT NULL,
PNAME CHAR(20) NOT NULL,
COLOR CHAR(8),
WEIGHT SMALLINT,
PRIMARY KEY(PNO));
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
PRIMARY KEY(JNO));
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
QTY SMALLINT,
PRIMARY KEY (SNO,PNO,JNO),
CONSTRAINT `spj_ibfk_1` FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `spj_ibfk_2` FOREIGN KEY (PNO) REFERENCES P(PNO) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `spj_ibfk_3` FOREIGN KEY (JNO) REFERENCES J(JNO) ON DELETE CASCADE ON UPDATE CASCADE);上面是我构建表的时候写的代码
表的内容如下查询的题目是------->>>>>找出供应工程J1零件为红色的供应商号码SNO我的查询代码是USE SPJ;
SELECT DISTINCT SNO FROM SPJ
WHERE JNO='J1' AND PNO IN
(SELECT PNO
FROM P
WHERE COLOR='红');
但是一直都查不到东西,但是如果我用
USE SPJ;
SELECT DISTINCT * FROM SPJ
WHERE JNO = 'J1' AND PNO IN ('P1', 'P4' , 'P6')
就可以成功查询,请求各位大佬指教
FROM P
WHERE COLOR='红'你现用这个语句查查有没有数据就知道了。
如果没有,再查找其他问题。
比如,你用的char定长的,把空格去掉再比较
然后我就用了
SELECT SNO
FROM SPJ
WHERE JNO = 'J1' AND PNO IN ('P1','P4','P6')结果是对的,但是唯独用我原来的就不行