你是一个食物批发商的数据库开发者.每星期,公司完成不同顾客的订单.通常,每个顾客每星期定购同样数量的一定项目.偶尔,顾客定购的某个项目的数量明显少于顾客通常的数量.订单的信息储存在表invoice中,该表在sqlserver2000数据库中.创建该表的脚步如x下:CREATE TABLE Invoice
(
InvoiceID int NOT NULL,
InvoiceNumber char(10) NOT NULL,
CustomerName char(30) NOT NULL,
InvoiceAmount money NOT NULL DEFAULT (0),
CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID)
)你想验证这些和平常不一样的订单的样品,为此,你必须为每个顾客生成一列货物,货物数量少于该顾客平均的货物数量.
你应该用哪个查询?
单项选择题
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1, Invoice AS i2
GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
HAVING i1.InvoiceAmount < AVG (i2.InvoiceAmount)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1
WHERE i1.InvoiceAmount <
(SELECT AVG (i2.InvoiceAmount)
FROM Invoice AS i2
WHERE i2.CustomerName=i1.CustomerName)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1
WHERE i1.InvoiceAmount <
(SELECT AVG (i2.InvoiceAmount)
FROM Invoice AS i2)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount,
CASE WHEN i1.InvoiceAmount < AVG (i2.InvoiceAmount)
THEN i1.InvoiceAmount ELSE 0 END FROM Invoice As i1 INNER JOIN Invoice AS i2
ON i1.CustomerName = i2.CustomerName
GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
ORDER BY i1.CustomerName, i1.InvoiceNumber
答案：B
【原题】：你是一个食物批发商的数据库开发者.每星期,公司完成不同顾客的订单.通常,每个顾客每星期定购同样数量的一定项目.偶尔,顾客定购的某个项目的数量明显少于顾客通常的数量.订单的信息储存在表invoice中,该表在sqlserver2000数据库中.创建该表的脚步如x下:CREATE TABLE Invoice
(
InvoiceID int NOT NULL,
InvoiceNumber char(10) NOT NULL,
CustomerName char(30) NOT NULL,
InvoiceAmount money NOT NULL DEFAULT (0),
CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID)
)你想验证这些和平常不一样的订单的样品,为此,你必须为每个顾客生成一列货物,货物数量少于该顾客平均的货物数量.
你应该用哪个查询?
单项选择题
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1, Invoice AS i2
GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
HAVING i1.InvoiceAmount < AVG (i2.InvoiceAmount)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1
WHERE i1.InvoiceAmount <
(SELECT AVG (i2.InvoiceAmount)
FROM Invoice AS i2
WHERE i2.CustomerName=i1.CustomerName)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
FROM Invoice As i1
WHERE i1.InvoiceAmount <
(SELECT AVG (i2.InvoiceAmount)
FROM Invoice AS i2)
ORDER BY i1.CustomerName, i1.InvoiceNumber
SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount,
CASE WHEN i1.InvoiceAmount < AVG (i2.InvoiceAmount)
THEN i1.InvoiceAmount ELSE 0 END FROM Invoice As i1 INNER JOIN Invoice AS i2
ON i1.CustomerName = i2.CustomerName
GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmount
ORDER BY i1.CustomerName, i1.InvoiceNumber
