模拟实战数据落地:MSsql通过存储过程获得销售数据视图
话不多说
目标需求:通过传递参数(查询条件及查询时间)调用存储过程获得销售数据视图,并且在视图中有时间字段供后续引用,实现数据对接获取任务
最终结果如图:
实现以上结果步骤如下:
1)建立users表和orders表分别代表用户及订单,其中订单中用户id与用户表中用户id关联,并随机插入一些测试数据,sql脚本如下:
-- 创建 Users 表
CREATE TABLE Users (UserId INT PRIMARY KEY IDENTITY(1,1),Username NVARCHAR(255) NOT NULL
);-- 创建 Orders 表
CREATE TABLE Orders (OrderId INT PRIMARY KEY IDENTITY(1,1),UserId INT NOT NULL,OrderTime DATETIME NOT NULL,Amount DECIMAL(18, 2) NOT NULL,Status NVARCHAR(50),CONSTRAINT FK_Orders_Users FOREIGN KEY (UserId) REFERENCES Users(UserId)
);-- 插入 Users 表的模拟数据
INSERT INTO Users (Username) VALUES ('Alice');
INSERT INTO Users (Username) VALUES ('Bob');
INSERT INTO Users (Username) VALUES ('Charlie');
INSERT INTO Users (Username) VALUES ('David');-- 插入 Orders 表的模拟数据
INSERT INTO Orders (UserId, OrderTime, Amount, Status) VALUES (1, '2024-01-02T12:00:00', 99.99, 'Shipped');
INSERT INTO Orders (UserId, OrderTime, Amount, Status) VALUES (2, '2024-01-02T15:30:00', 199.99, 'Processing');
INSERT INTO Orders (UserId, OrderTime, Amount, Status) VALUES (1, '2024-01-03T09:15:00', 249.99, 'Shipped');
INSERT INTO Orders (UserId, OrderTime, Amount, Status) VALUES (3, '2024-01-04T18:45:00', 39.99, 'Delivered');
INSERT INTO Orders (UserId, OrderTime, Amount, Status) VALUES (4, '2024-01-05T11:00:00', 59.99, 'Shipped');建表插入数据结果如下:
订单表:
用户表:
2)写存储过程,实现查询数据生成视图,关键是要将查询时间也写入到视图
存储过程代码如下:
USE [agui_conn]
GO
/****** Object: StoredProcedure [dbo].[sp_GetOrdersByTimestamp] Script Date: 09/28/2024 13:17:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetOrdersByTimestamp]@Timestamp NVARCHAR(100),@Condition NVARCHAR(100) -- 假设这是一个额外的条件，如订单状态
AS
BEGINIF OBJECT_ID('dbo.vw_OrdersByTimestamp', 'V') IS NOT NULLBEGINEXEC sp_executesql N'DROP VIEW dbo.vw_OrdersByTimestamp';END-- 创建一个视图来显示所需的订单信息DECLARE @sql NVARCHAR(MAX);SET @sql = N'CREATE VIEW dbo.vw_OrdersByTimestamp ASSELECT u.Username as 用户名, o.OrderTime as 订单时间,o.Amount as 订单金额,'''+ @Timestamp + ''' AS 查询时间FROM Orders oINNER JOIN Users u ON o.UserId = u.UserIdWHERE (o.Status = ''' + @Condition+ ''')'; -- 假设订单表中有一个Status字段o.OrderTime >= ''' + CONVERT(NVARCHAR, @Timestamp, 120) + ''' ANDEXEC sp_executesql @sql;END3)测试执行存储过程语句
EXEC sp_GetOrdersByTimestamp @Timestamp = '2024-09-28 16:27:10.000', @Condition = 'Shipped';--'Delivered';--执行结果:
以上存储过程是重点,重点,重点!!!
关键有个小问题未解决,不知怎样能不重建视图,而不断增加查询记录,查了很多资料都说用触发器或临时表,还在探索