mysql - 如何为所需的输出创建视图

Question

我应该在我的视图中包含什么，以便它在 9 月为员工 ID 4 输出交易数量：

SELECT * FROM DeniseTransactions;

给出输出：

  CREATE TABLE Transactions(
    e_id INT,
    c_id INT,
    l_id INT,
    date DATE,
    t_id INT,
    PRIMARY KEY(t_id),  
    FOREIGN KEY(c_id) references Customers(c_id),
    FOREIGN KEY(e_id) references Employees(e_id),
    FOREIGN KEY(l_id) references Locations(l_id)
    );

    INSERT INTO Transactions VALUES(1,3,1,'2021-08-09',1);
    INSERT INTO Transactions VALUES(4,2,2,'2021-08-14',2);
    INSERT INTO Transactions VALUES(4,4,1,'2021-09-07',3);
    INSERT INTO Transactions VALUES(3,4,1,'2021-09-07',4);
    INSERT INTO Transactions VALUES(4,1,3,'2021-09-07',5);
    INSERT INTO Transactions VALUES(1,4,1,'2021-09-23',6);

我知道它不正确，但这是我到目前为止所拥有的。只是努力走得更远

CREATE VIEW DeniseTransactions AS
    SELECT COUNT(transactions)
    FROM Transactions
    WHERE e_id = '4';




  

标签： mysqlsqlselectview

Answer 1

您可以将视图创建为：

create view DeniseTransactions as
select count(*) as number_of_transactions
from Transactions
where t_id = 4
  and date >= '2021-09-01' and date < '2021-10-01';

结果：

number_of_transactions
----------------------
1

请参阅DB Fiddle上的运行示例。