树结构表
USE [DEMO]
GO
/****** Object: Table [dbo].[Design_DrawingData] Script Date: 07/05/2019 18:13:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Design_DrawingData](
[DrawingId] [int] IDENTITY(1,1) NOT NULL,
[DrawingCode] [varchar](200) NOT NULL,
[ProjectId] [int] NOT NULL,
[DrawingName] [varchar](500) NULL,
[ParentDrawingId] [varchar](200) NOT NULL,
[DrawingLeave] [int] NULL,
[DrawingOrder] [int] NULL,
[CommitStartTime] [datetime] NULL,
[CommitEndTime] [datetime] NULL,
[AddUserId] [int] NOT NULL,
[UpdateTime] [datetime] NULL,
[FilePath] [varchar](500) NULL,
[Note] [varchar](max) NULL,
[BgColor] [varchar](50) NULL,
[FgColor] [varchar](50) NULL,
[Other] [varchar](max) NULL,
CONSTRAINT [PK_DESIGN_DRAWINGDATA] PRIMARY KEY CLUSTERED
(
[DrawingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'图纸唯一主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'DrawingId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'层级编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'DrawingCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'ProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'层级名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'DrawingName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所属父级层级编号(根级为0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'ParentDrawingId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'目录层级(根级为0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'DrawingLeave'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'层级排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'DrawingOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交付开始时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'CommitStartTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交付结束时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'CommitEndTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'AddUserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'UpdateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'图纸路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'FilePath'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'Note'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点背景色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'BgColor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点字体色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'FgColor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备用字段' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData', @level2type=N'COLUMN',@level2name=N'Other'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设计管理_图纸管理' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Design_DrawingData'
GO
外键表
USE [DEMO]
GO
DROP TABLE [dbo].[Advice_Problem]
/****** Object: Table [dbo].[Advice_Problem] Script Date: 07/03/2019 15:25:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Advice_Problem](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DrawingId] [int] NULL,
[ProblemType] [varchar](500) NULL,
[ProblemCount] [int] NOT NULL,
[Other] [varchar](max) NULL,
[ProjectId] [int] NULL,
CONSTRAINT [PK_Advice_Problem] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'图纸ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem', @level2type=N'COLUMN',@level2name=N'DrawingId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'问题种类' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem', @level2type=N'COLUMN',@level2name=N'ProblemType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'问题数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem', @level2type=N'COLUMN',@level2name=N'ProblemCount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem', @level2type=N'COLUMN',@level2name=N'Other'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem', @level2type=N'COLUMN',@level2name=N'ProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'咨询管理' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Advice_Problem'
GO
ALTER TABLE [dbo].[Advice_Problem] ADD CONSTRAINT [DF_Advice_Problem_ProblemCount] DEFAULT ((0)) FOR [ProblemCount]
GO
存储过程
USE [DEMO]
GO
/****** Object: StoredProcedure [dbo].[Report_DesignDrawing] Script Date: 07/03/2019 15:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,YFH>
-- Create date: <Create Date,2017.09.16>
-- Description: <Description,设计问题统计>
-- =============================================
CREATE PROC [dbo].[Report_DesignDrawing]
(
@projectId int,--项目id
@keyWords Varchar(50)--搜索关键字
)
AS
BEGIN
create table #temptb --创建临时表,列名最好与树目录列对应
(
Id int identity(1,1) not null,
DrawingId int,
DrawingCode varchar(200),
DrawingName varchar(200),
DrawingLeave int,
ParentDrawingId int
);
--动态创建列,用于统计页面除主要字段其他统计字段数量不确定的情况,其他情况直接创建静态列
declare @sql as varchar(1000)
declare @sqlupdate as varchar(1000)
declare @i as int
declare @count as varchar(2)
set @i = 1
set @count = (select count(distinct(ProblemType)) from dbo.Advice_Problem where ProjectId=@projectId)
while @i <= @count
begin
SET @sql = 'ALTER TABLE #temptb ADD [P'+ convert(varchar(10),@i) +'] NVARCHAR(100) NULL'
execute (@sql)
set @i = @i + 1
end
if @keyWords is null or @keyWords='' --判断搜索参数是否为空
begin
WITH TEMP AS
(
SELECT * FROM Design_DrawingData WHERE ProjectId=@projectId and DrawingId=(select min(DrawingId) from Design_DrawingData where ProjectId=@projectId)
UNION ALL
SELECT T0.* FROM TEMP,Design_DrawingData T0 WHERE TEMP.DrawingId=T0.ParentDrawingId
)
insert into #temptb(DrawingId,DrawingCode,DrawingName,DrawingLeave,ParentDrawingId)
SELECT TEMP.DrawingId,TEMP.DrawingCode,TEMP.DrawingName,TEMP.DrawingLeave,TEMP.ParentDrawingId FROM TEMP;
end
else
begin
WITH TEMP AS
(
SELECT * FROM Design_DrawingData WHERE ProjectId=1 and (DrawingName like '%'+@keyWords+'' or DrawingCode like '%'+@keyWords+'')
UNION ALL
SELECT T0.* FROM TEMP,Design_DrawingData T0 WHERE TEMP.DrawingId=T0.ParentDrawingId
)
insert into #temptb(DrawingId,DrawingCode,DrawingName,DrawingLeave,ParentDrawingId)
SELECT distinct(TEMP.DrawingId),TEMP.DrawingCode,TEMP.DrawingName,TEMP.DrawingLeave,TEMP.ParentDrawingId FROM TEMP where DrawingLeave<6;
end
--定义循环时临时变量
Declare @total int
Declare @currentIndex int
Declare @totalRows int
Declare @DrawingId int
select @currentIndex=1
select @totalRows=count(1) from #temptb
while(@currentIndex<=@totalRows) --循环每一个树节点
begin
select @DrawingId= DrawingId from #temptb where id=@currentIndex;
WITH TEMP AS
(
SELECT * FROM Design_DrawingData WHERE DrawingId=@DrawingId
UNION ALL
SELECT T0.* FROM TEMP,Design_DrawingData T0 WHERE TEMP.DrawingId=T0.ParentDrawingId
)
select @total =(select COUNT(1) from TEMP)
set @i=1
while @i <= @count --循环更新该节点下每个统计列的值,如果统计列固定,则不需要循环
begin
SET @sql = 'update #temptb set P'+convert(varchar(10),@i)+'=(select ProblemCount from dbo.Advice_Problem as f where f.DrawingId ='+convert(varchar(10),@DrawingId)+' and f.ProblemType=''P'+convert(varchar(10),@i)+''') where DrawingId='+convert(varchar(10),@DrawingId)+''
execute (@sql)
SET @sqlupdate = 'update #temptb set P'+convert(varchar(10),@i)+'=0 where DrawingId='+convert(varchar(10),@DrawingId)+' and P'+convert(varchar(10),@i)+' is null'
execute (@sqlupdate)
set @i = @i + 1
end
select @currentIndex=@currentIndex+1;
end
select * from #temptb;
DROP TABLE #temptb
END