主页 / dba / 问题 / 184478
Accepted
learningIT
Asked: 2017-08-29 02:53:55 +0800 CST

T-SQL 减号运算符

  • 772

这些是我创建并在其中插入值的表：

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO Customer (Customer_Name)
VALUES('Ornob')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD (DVD_Name)
VALUES('Rurouni Kenshin')


INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,4)

为了显示没有使用 MINUS 运算符购买任何 DVD 的客户名称，这是我尝试过的：

SELECT Customer_Name
FROM Customer
WHERE Customer_No 
IN
(SELECT Customer_No FROM Customer
 MINUS
 SELECT Customer_No FROM DVD_Purchase
)

我收到以下错误消息：

消息 156，级别 15，状态 1，第 123 行关键字“SELECT”附近的语法不正确。消息 102，级别 15，状态 1，第 124 行 ')' 附近的语法不正确。

如错误消息中所示，')' 附近的语法不正确，我无法弄清楚。

除此之外，有人可以为我提供正确的查询吗？

sql-server sql-server-2014

1 个回答

  1. Best Answer

    MINUS是“设置差异”运算符的名称，仅供 Oracle 使用。

    SQL Server 使用（标准）名称EXCEPT, 来表示此运算符。替换为MINUS将EXCEPT解决问题：

    SELECT Customer_Name
FROM Customer
WHERE Customer_No 
  IN
  (SELECT Customer_No FROM Customer
   EXCEPT
   SELECT Customer_No FROM DVD_Purchase
  ) ;

    顺便说一句，由于外部查询和子查询引用了同一个表+列（Customer . Customer_No），您可以NOT IN不使用EXCEPT. 假设所涉及的列 ( Customer_No) 不可为空，这是等价的：

    SELECT Customer_Name
FROM Customer
WHERE Customer_No 
  NOT IN
  (SELECT Customer_No FROM DVD_Purchase
  ) ;
    • 12

相关问题