如何输出在第X天下的所有订单的列表，以计算维修的总次数和花费在这些维修上的总金额

Question

你能帮我解决这个问题吗：

Make a list of all orders placed on day X as
calculate the total number of repairs and the total amount spent on them

这就是代码：

CREATE TABLE Employee (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName  NVARCHAR(50) NOT NULL,
LastName  NVARCHAR(50) NOT NULL,
);

CREATE TABLE Orders (
ID INT IDENTITY NOT NULL PRIMARY KEY,
Date_of_acceptance DATE,
Date_of_deadline DATE,
Status_order NVARCHAR(50) NOT NULL,
Type_of_repair NVARCHAR (50) NOT NULL,
Price DECIMAL (20),
EmployeeID INT,
CustomerID INT,
ItemsID INT
);


ALTER TABLE Orders ALTER COLUMN Price DECIMAL (5,2)


CREATE TABLE Customer (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName  NVARCHAR(50) NOT NULL,
LastName  NVARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(20)

);


CREATE TABLE Items (
ID INT IDENTITY NOT NULL PRIMARY KEY,
ItemsName NVARCHAR(50) NOT NULL,
Status_items NVARCHAR(50) NOT NULL
);




 ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Items FOREIGN KEY (ItemsID)
REFERENCES Items(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Printer', 'Accept for the repair ');
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Computer', 'Unclaimed');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Laptop', 'Accept for the repair');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Battery' , 'Unclaimed' );
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Computer' , 'Accept for repair' );
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Monitor', 'Unclaimed');

SELECT * FROM Items;
UPDATE Items SET Status_items ='Accept for repair' WHERE ID=7;




INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price, EmployeeID, CustomerID, ItemsID)
    VALUES('2021-11-16', '2021-11-18','Accept','Broken screen of laptop', 100, 1,1,7);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-10-9', '2021-10-15','Submitted','Broken printer',90,1,1,1);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-5-16', '2021-5-30','Waiting for delivery of part','Rеplacing the motherboard',500,1,1,6);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-8-2', '2021-8-26','Accept','Repair a broken computer',600, 2,2,2);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES('2021-7-12', '2021-7-14','Accept','Change the laptop battery', 120,2,2,5);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-10-9', '2021-10-15','Submitted','Update windows',30,2,2,6);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-2-6', '2021-2-21','Waiting for delivery of part',' Rеplacing the motherboard ',500,3,3,3);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-4-3', '2021-4-25','Accept',' Virus scan',25,3,3,3);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-5-16', '2021-5-30','Accept',' Change the laptop battery',120,3,3,5);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-10-9', '2021-10-15','Submitted','Broken printer',90,3,3,1);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-10-5', '2021-10-7','Accept','Broken screen',90,3,3,7);
    INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
    VALUES ('2021-3-5', '2021-3-8','Accept',' Rеplacing the motherboard',90,3,3,2);
    
    SELECT * FROM Orders;
    UPDATE Orders SET Date_of_acceptance ='2021-10-9' WHERE ID=7;
    UPDATE Orders SET Date_of_deadline ='2021-10-15' WHERE ID=7;

我这样做了，但我不知道这样做对不对，我不知道如何修复它：

SELECT Date_of_deadline,Type_of_repair,Price
FROM Orders 
WHERE  Orders.Status_order  = 'Submitted'(
    SELECT SUM(Price) AS 'Sum of repairs'
    FROM Orders
    WHERE  Orders.Status_order  = 'Submitted')

Answer 1

我被你的代码搞糊涂了，你做了两个独立的查询，对吗？

我想您忘了在第二个select as上添加count(1)来计算订单总数，并添加date as where参数使其等于X