模拟实战数据落地: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
BEGIN
	IF OBJECT_ID('dbo.vw_OrdersByTimestamp', 'V') IS NOT NULL
	BEGIN
		EXEC sp_executesql N'DROP VIEW dbo.vw_OrdersByTimestamp';
	END
	-- 创建一个视图来显示所需的订单信息
	DECLARE @sql NVARCHAR(MAX);
	SET @sql = N'CREATE VIEW dbo.vw_OrdersByTimestamp AS
	SELECT 
		u.Username as 用户名, 
		o.OrderTime as 订单时间,
		o.Amount as 订单金额,
		'''+ @Timestamp + ''' AS 查询时间
	FROM 
		Orders o
	INNER JOIN 
		Users u ON o.UserId = u.UserId
	WHERE 
		(o.Status = ''' + @Condition+ ''')'; -- 假设订单表中有一个Status字段o.OrderTime >= ''' + CONVERT(NVARCHAR, @Timestamp, 120) + ''' AND
	EXEC sp_executesql @sql;

	
END

3)测试执行存储过程语句

EXEC sp_GetOrdersByTimestamp 
	@Timestamp = '2024-09-28 16:27:10.000', 
	@Condition = 'Shipped';--'Delivered';--

执行结果:

以上存储过程是重点,重点,重点!!!

关键有个小问题未解决,不知怎样能不重建视图,而不断增加查询记录,查了很多资料都说用触发器或临时表,还在探索