时间戳

首页 > 解决方案 > 历史报表的动态设计

sql - 历史报表的动态设计

问题描述

我正在为我当前的项目创建历史报告。由于它是机密的，我将用学生主题示例进行解释。Student、Subject 和 StudentSubject 是三个表，_Audit 是它们各自的审计表，审计跟踪信息将存储在_审计表中。

CREATE TABLE [dbo].[AudRel](
    [AudId] [int] IDENTITY(1,1) NOT NULL,
    [AudTableName] [varchar](100) NULL,
    [AudFieldName] [varchar](100) NULL,
    [AudFieldID] [varchar](30) NULL,
 CONSTRAINT [PK_AuditRel] PRIMARY KEY CLUSTERED 
(
    [AudId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Student](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [varchar](100) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Student_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [StudentName] [varchar](100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[StudentSubject](
    [SSID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NULL,
    [SubjectID] [int] NULL,
 CONSTRAINT [PK_StudentSubject] PRIMARY KEY CLUSTERED 
(
    [SSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[StudentSubject_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SSID] [int] NOT NULL,
    [StudentID] [int] NULL,
    [SubjectID] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Subject](
    [SubjectID] [int] IDENTITY(1,1) NOT NULL,
    [SubjectName] [varchar](50) NULL,
 CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED 
(
    [SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Subject_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SubjectID] [int] NOT NULL,
    [SubjectName] [varchar](50) NULL
) ON [PRIMARY]


SET IDENTITY_INSERT [dbo].[AudRel] ON 
INSERT [dbo].[AudRel] ([AudId], [AudTableName], [AudFieldName], [AudFieldID]) VALUES (1, N'Student', N'StudentName', N'StudentID')
INSERT [dbo].[AudRel] ([AudId], [AudTableName], [AudFieldName], [AudFieldID]) VALUES (2, N'Subject', N'SubjectName', N'SubjectID')


SET IDENTITY_INSERT [dbo].[AudRel] OFF

SET IDENTITY_INSERT [dbo].[Student] ON 
INSERT [dbo].[Student] ([StudentID], [StudentName]) VALUES (1, N'Alex')
INSERT [dbo].[Student] ([StudentID], [StudentName]) VALUES (2, N'DSouza')
SET IDENTITY_INSERT [dbo].[Student] OFF

SET IDENTITY_INSERT [dbo].[StudentSubject] ON 
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (1, 1, 1)
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (2, 2, 1)
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (3, 2, 2)
SET IDENTITY_INSERT [dbo].[StudentSubject] OFF

SET IDENTITY_INSERT [dbo].[Subject] ON 
INSERT [dbo].[Subject] ([SubjectID], [SubjectName]) VALUES (1, N'English')
INSERT [dbo].[Subject] ([SubjectID], [SubjectName]) VALUES (2, N'Mathematics')
SET IDENTITY_INSERT [dbo].[Subject] OFF

我使用下面的动态脚本，它完美适用于 Student 和 SUbject 等独立表，但这种设计不适用于 StudentSubject，因为它是一个关系表，如何动态地将这个表适应现有设计，任何建议。我需要重新设计审计关系表吗，请指教

DECLARE @TableName VARCHAR(100),@FieldName VARCHAR(100),@FieldID VARCHAR(100)

SELECT @TableName = [AudTableName]
, @FieldName=[AudFieldName]
, @FieldID=[AudFieldID] 
FROM [dbo].[AudRel] WHERE [AudId] = 1 -- (Ex : StudentHistory)


DECLARE @SQL NVARCHAR(MAX) = N'
 SELECT ID,'  + @FieldID +
','  + @FieldName + ' FROM ' +  @TableName + '_Audit ' + ' WHERE ' + @FieldID + ' = ' 
+ Convert(varchar(20),@FieldID) 

print @SQL
EXECUTE sp_executesql @SQL

同样我需要修改动态脚本吗

标签： sqlsql-server

解决方案

推荐阅读