你是一个食物批发商的数据库开发者.每星期,公司完成不同顾客的订单.通常,每个顾客每星

11 查阅

你是一个食物批发商的数据库开发者.每星期,公司完成不同顾客的订单.通常,每个顾客每星期定购同样数量的一定项目.偶尔,顾客定购的某个项目的数量明显少于顾客通常的数量.订单的信息储存在表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.InvoiceAmountFROM Invoice As i1, Invoice AS i2GROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmountHAVING i1.InvoiceAmount < AVG (i2.InvoiceAmount)ORDER BY i1.CustomerName, i1.InvoiceNumber

SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmountFROM Invoice As i1WHERE i1.InvoiceAmount <(SELECT AVG (i2.InvoiceAmount)FROM Invoice AS i2WHERE i2.CustomerName=i1.CustomerName)ORDER BY i1.CustomerName, i1.InvoiceNumber

SELECT i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmountFROM Invoice As i1WHERE 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 i2ON i1.CustomerName = i2.CustomerNameGROUP BY i1.InvoiceNumber, i1.CustomerName, i1.InvoiceAmountORDER BY i1.CustomerName, i1.InvoiceNumber

参考答案:

SQL试