SQL Server 实现业务单据与业务单据(上一版本)之间的数据差异

最新推荐文章于 2022-06-02 11:07:44 发布
最新推荐文章于 2022-06-02 11:07:44 发布
阅读量385 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chenshanliang/article/details/84997457
版权

背景

       在公司的一个项目的开发有一个业务单据在一个状态后必需升级版本才可以修改数据，另需在当前版本上显示与上一版的数据(每一个字段的)差异，如果是用SQL语写死的话那以后新加一个表(或字段)都要去修改对应的SQL,有没有可参数化可配置的方法呢？

 

实现

      实现方法主要是利用了SQL SERVER Josn功能，所以必需是SQL SERVER 2016+

      记得刚了解Sql Server JSON 时候，可以将其转为行记录如下：

declare @str nvarchar(1000)='{"SampSerial":"3U8S9TFM","SampNo":"7ERX1WIH","SampVer":34719,"DesiVer":44243,"DocType":"RKUJIT7G"}'

select [key],[value] from openjson(@str)


key                                                                                                                                                                                                                                                              value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SampSerial                                                                                                                                                                                                                                                       3U8S9TFM
SampNo                                                                                                                                                                                                                                                           7ERX1WIH
SampVer                                                                                                                                                                                                                                                          34719
DesiVer                                                                                                                                                                                                                                                          44243
DocType

如果key是字段名我们可以很方便对比每列的值，下面我们看实例

1.创建一个函数，方便转为行记录

create function [dbo].[fu_read](@str nvarchar(max))
returns table
as
	return(
	select *
	from openjson(SUBSTRING(@str,2,len(@str)-2))
	)

2.实例代码

drop table if exists #test
drop table if exists #old
drop table if exists #new


select docno='A',ver=1,ctype='a',qty=8 into #test union all
select docno='A',ver=2,ctype='b',qty=10 

SELECT docno,[OldVal]=(select ctype,qty from #test b where a.docno=b.docno and a.ver=b.ver  for JSON path)
into #old
FROM #test a
where docno='A' and ver=1
 
SELECT docno,[NewVal]=(select ctype,qty from #test b where a.docno=b.docno and a.ver=b.ver  for JSON path)
into #new
FROM #test a
where docno='A' and ver=2
 
;
with old as(
	select a.docno,[key],[value]
	from #old a
	cross apply dbo.fu_read(a.[OldVal]) b
),new as(
	select a.docno,[key],[value]
	from #new a
	cross apply dbo.fu_read(a.[NewVal]) b
)
select a.docno,a.[key],oldval=a.[value],newval=b.[value]
from old a
inner join new b on a.docno=b.docno and a.[key]=b.[key]
where a.[value]<>b.[value]

如果想要可参数化可配置，改为动态SQL(1.表联取条件，2.转为JOSN字段列),下面是整个开发中的实例

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fu_read](@str nvarchar(max))
returns table
as
	return(
	select *
	from openjson(SUBSTRING(@str,2,len(@str)-2))
	)
GO
/****** Object:  Table [dbo].[PdCompareH]    Script Date: 2018-12-14 11:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PdCompareH](
	[Id] [uniqueidentifier] NOT NULL,
	[AccId] [smallint] NOT NULL,
	[CompareId] [int] NOT NULL,
	[CompareTab] [varchar](100) NOT NULL,
	[CompareDesc] [nvarchar](200) NOT NULL,
	[OrgTab] [varchar](100) NULL,
	[IsShow1] [bit] NOT NULL,
	[IsShow2] [bit] NOT NULL,
	[IsShow3] [bit] NOT NULL,
	[IsShow4] [bit] NOT NULL,
	[IsShow5] [bit] NOT NULL,
	[IsShow6] [bit] NOT NULL,
	[IsShow7] [bit] NOT NULL,
	[IsShow8] [bit] NOT NULL,
	[OrderId] [int] NULL,
	[Remark] [nvarchar](500) NULL,
	[Creator] [nvarchar](50) NULL,
	[CreatorCode] [varchar](50) NULL,
	[CreateDate] [datetime] NULL,
	[Updater] [nvarchar](50) NULL,
	[UpdaterCode] [varchar](50) NULL,
	[UpdateDate] [datetime] NULL,
	[IsDelete] [tinyint] NOT NULL,
	[OperDept] [nvarchar](20) NULL,
	[RoCode] [varchar](50) NULL,
 CONSTRAINT [PK_PdCompareH] PRIMARY KEY CLUSTERED 
(
	[CompareId] 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
/****** Object:  Table [dbo].[PdCompareS]    Script Date: 2018-12-14 11:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PdCompareS](
	[Id] [uniqueidentifier] NOT NULL,
	[AccId] [smallint] NOT NULL,
	[CompareId] [int] NOT NULL,
	[ColCode] [varchar](50) NOT NULL,
	[ColName] [nvarchar](100) NOT NULL,
	[IsKey] [bit] NOT NULL,
	[IsCompare] [bit] NOT NULL,
	[IsShow1] [bit] NOT NULL,
	[IsShow2] [bit] NOT NULL,
	[IsShow3] [bit] NOT NULL,
	[IsShow4] [bit] NOT NULL,
	[IsShow5] [bit] NOT NULL,
	[IsShow6] [bit] NOT NULL,
	[IsShow7] [bit] NOT NULL,
	[IsShow8] [bit] NOT NULL,
	[Creator] [nvarchar](50) NULL,
	[CreatorCode] [varchar](50) NULL,
	[CreateDate] [datetime] NULL,
	[Updater] [nvarchar](50) NULL,
	[UpdaterCode] [varchar](50) NULL,
	[UpdateDate] [datetime] NULL,
	[IsDelete] [tinyint] NOT NULL,
	[OperDept] [nvarchar](20) NULL,
	[RoCode] [varchar](50) NULL,
 CONSTRAINT [PK_PdCompareS] PRIMARY KEY CLUSTERED 
(
	[CompareId] ASC,
	[ColCode] 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
/****** Object:  Table [dbo].[PdDiffDataH]    Script Date: 2018-12-14 11:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PdDiffDataH](
	[AccId] [smallint] NOT NULL,
	[SampSerial] [varchar](50) NOT NULL,
	[SampSerialOrg] [varchar](50) NOT NULL,
 CONSTRAINT [PK_PdDiffDataH] PRIMARY KEY CLUSTERED 
(
	[SampSerial] 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
/****** Object:  Table [dbo].[PdDiffDataS]    Script Date: 2018-12-14 11:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PdDiffDataS](
	[AccId] [smallint] NOT NULL,
	[SampSerial] [varchar](50) NOT NULL,
	[CompareId] [int] NOT NULL,
	[KeyVal] [varchar](100) NOT NULL,
	[ColCode] [varchar](50) NOT NULL,
	[NewVal] [nvarchar](max) NULL,
	[OldVal] [nvarchar](max) NULL,
	[DiffType] [smallint] NOT NULL,
	[UpdaterCode] [varchar](50) NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [PK_PdDiffDataS] PRIMARY KEY CLUSTERED 
(
	[SampSerial] ASC,
	[CompareId] ASC,
	[KeyVal] ASC,
	[ColCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PdSampleH]    Script Date: 2018-12-14 11:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PdSampleProductColor](
	[Id] [uniqueidentifier] NOT NULL,
	[AccId] [smallint] NOT NULL,
	[SampSerial] [varchar](50) NOT NULL,
	[SampGLine] [int] NOT NULL,
	[AlterDesc] [nvarchar](500) NULL,
	[WoolenColor] [nvarchar](100) NOT NULL,
	[IsHead] [bit] NOT NULL,
	[IsCancelColor] [bit] NOT NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLightColour] [bit] NOT NULL,
	[ColorCode] [varchar](50) NOT NULL,
	[ColorName] [nvarchar](200) NOT NULL,
	[Total] [int] NOT NULL,
	[HeadQty] [int] NULL,
	[ColorQty] [int] NULL,
	[SingleQty] [int] NULL,
	[FastQty] [int] NULL,
	[Size1] [int] NULL,
	[Size2] [int] NULL,
	[Size3] [int] NULL,
	[Size4] [int] NULL,
	[Size5] [int] NULL,
	[Size6] [int] NULL,
	[Size7] [int] NULL,
	[Size8] [int] NULL,
	[Size9] [int] NULL,
	[Size10] [int] NULL,
	[Size11] [int] NULL,
	[Size12] [int] NULL,
	[Size13] [int] NULL,
	[Size14] [int] NULL,
	[Size15] [int] NULL,
	[Size16] [int] NULL,
	[Size17] [int] NULL,
	[Size18] [int] NULL,
	[Size19] [int] NULL,
	[Size20] [int] NULL,
	[Size21] [int] NULL,
	[Size22] [int] NULL,
	[Size23] [int] NULL,
	[Size24] [int] NULL,
	[Size25] [int] NULL,
	[Size26] [int] NULL,
	[Size27] [int] NULL,
	[Size28] [int] NULL,
	[Size29] [int] NULL,
	[Size30] [int] NULL,
	[Creator] [nvarchar](50) NULL,
	[CreatorCode] [nvarchar](50) NULL,
	[CreateDate] [datetime] NULL,
	[Updater] [nvarchar](50) NULL,
	[UpdaterCode] [nvarchar](50) NULL,
	[UpdateDate] [datetime] NULL,
	[IsDelete] [tinyint] NOT NULL,
	[OperDept] [nvarchar](50) NULL,
	[RoCode] [varchar](50) NULL,
 CONSTRAINT [PK_PdSampleProductColor] PRIMARY KEY CLUSTERED 
(
	[SampSerial] ASC,
	[SampGLine] 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








/***********************************************************************************************************************************************
功能:比较板单上版本数据差异



***********************************************************************************************************************************************/
CREATE proc [dbo].[up_PdSampleVerDiff](
	@AccId smallint,				--AccId 
	@CompareId int=1,				--比较ID PdCompareH.CompareId
	@SampSerial varchar(50),		--板单流水号
	@WhereStr nvarchar(max),		--异动数据记录的WHERE条件
	@User varchar(50),				--更新人
	@DateTime datetime,				--更新时间
	@Err nvarchar(2000)	out			--返回错误信息
)
as
/*
declare
	@AccId smallint=1,
	@CompareId int=2,
	@SampSerial varchar(50)='MO1805020005',
	@WhereStr nvarchar(max)='  SampSerial=''MO1805020005'' ',
	@User varchar(50)='admin8',	
	@DateTime datetime=getdate(),
	@Err nvarchar(2000)
--*/
declare @SampSerialOrg varchar(50),@StyleBigClass varchar(50)


/* 上版本的取法
设计板单(非套装)：板单号+板类+版本
设计板单(套装)：板单号+板类+版本+款号流水号
制作板单：板单号+板类+款号流水号+版本+其他描述
*/

select @SampSerialOrg=isnull(SampSerialOrg,'-') from PdDiffDataH(nolock) where SampSerial=@SampSerial
if @SampSerialOrg='-' return;
if ISNULL(@SampSerialOrg,'')='' begin
	set @StyleBigClass=(select StyleBCode from PdSampleGoodsAttr where SampSerial=@SampSerial)

	set @SampSerialOrg=(select top 1 a.SampSerial 
						from PdSampleH a(nolock)
						inner join PdSampleH b(nolock) on a.SampNo=b.SampNo and a.SampCCode=b.SampCCode and a.AccId=b.AccId
							and case when b.SampCCode in('MBT07') and @StyleBigClass<>'StyleBigClass_4' then '' else a.GoodsSerial end=case when b.SampCCode in('MBT07')  and @StyleBigClass<>'StyleBigClass_4' then '' else b.GoodsSerial end
							and case when b.SampCCode in('MBT07') then '' else isnull(a.OtherDesc,'') end=case when b.SampCCode in('MBT07') then '' else isnull(b.OtherDesc,'') end
						where b.SampSerial=@SampSerial and a.SampVer<b.SampVer and a.IsDelete=0
						order by a.SampVer desc)
	
	insert into PdDiffDataH([AccId],[SampSerial],[SampSerialOrg])
	values(@AccId,@SampSerial,isnull(@SampSerialOrg,'-'))
	if ISNULL(@SampSerialOrg,'')='' return;
end

/***************************************************************************************************************************************************************/
declare @KeyList nvarchar(2000),@CompareList nvarchar(max),@CompareTab varchar(100)
declare @sql nvarchar(max)

select @KeyList=isnull(KeyList,''''''),@CompareList=CompareList,@CompareTab=CompareTab
from vwPdCompareH
where AccId=@AccId and CompareId=@CompareId

if @CompareTab is null begin
	set @Err='未配置对比Id:'+CAST(@CompareId as nvarchar(10))+''
	return;
end
--print @CompareList
if LTRIM(@WhereStr)>'' set @WhereStr=' and '+@WhereStr

set @sql='
drop table if exists #newdata
select SampSerial,'+@KeyList+' as KeyVal,IsDelete,'+@CompareList+'
into #newdata
from '+@CompareTab+' (nolock)
where SampSerial='''+@SampSerial+''' '+@WhereStr+'

drop table if exists #olddata;
select SampSerial,'+@KeyList+' as KeyVal,IsDelete,'+@CompareList+'
into #olddata
from '+@CompareTab+' (nolock)
where SampSerial='''+@SampSerialOrg+''' and '+@KeyList+' in (select '+@KeyList+' from #newdata) 


begin try
	--清除已存在的差差异数据
	--delete PdDiffDataS where SampSerial=@SampSerial and CompareId=@CompareId and KeyVal in(select KeyVal from #newdata);

	drop table if exists #tempPdDiffDataS
	select top 0 [AccId],[SampSerial],[CompareId],[KeyVal],[ColCode],[NewVal],[OldVal],[DiffType]
	into #tempPdDiffDataS 
	from PdDiffDataS
	--新增
	insert into #tempPdDiffDataS ([AccId],[SampSerial],[CompareId],[KeyVal],[ColCode],[NewVal],[OldVal],[DiffType])
	select [AccId]=@AccId,[SampSerial],[CompareId]=@CompareId,[KeyVal],[ColCode]=''All''
		,[NewVal]=(select '+@CompareList+' from #newdata a where a.KeyVal=#newdata.KeyVal for JSON path)
		,[OldVal]=null,[DiffType]=1
	from #newdata
	where KeyVal not in(select KeyVal from #olddata where IsDelete=0) and IsDelete=0

	--删除
	insert into #tempPdDiffDataS ([AccId],[SampSerial],[CompareId],[KeyVal],[ColCode],[NewVal],[OldVal],[DiffType])
	select [AccId]=@AccId,[SampSerial],[CompareId]=@CompareId,[KeyVal],[ColCode]=''All''
		,[NewVal]=(select '+@CompareList+' from #newdata a where a.KeyVal=#newdata.KeyVal for JSON path)
		,[OldVal]=null,[DiffType]=2
	from #newdata
	where KeyVal in(select KeyVal from #olddata  where IsDelete=0) and IsDelete=1

	--更新
	;
	with newdata as(
		select a.[KeyVal],b.[Key],b.value
		from (
			select [KeyVal],Detail=(select '+@CompareList+' from #newdata where #newdata.[KeyVal]=a.[KeyVal] for JSON path,INCLUDE_NULL_VALUES)
			from #newdata a 
			where isdelete=0 ) a
		cross apply dbo.fu_read(a.detail) b
	
	),olddata as(
		select a.[KeyVal],b.[Key],b.value
		from (
			select [KeyVal],Detail=(select '+@CompareList+' from #olddata where #olddata.[KeyVal]=a.[KeyVal] for JSON path,INCLUDE_NULL_VALUES)
			from #olddata a 
			where IsDelete=0) a
		cross apply dbo.fu_read(a.detail) b
	)
	insert into #tempPdDiffDataS ([AccId],[SampSerial],[CompareId],[KeyVal],[ColCode],[NewVal],[OldVal],[DiffType])
	select [AccId]=@AccId,[SampSerial]=@SampSerial,[CompareId]=@CompareId,[KeyVal]=n.[KeyVal],[ColCode]=n.[Key],[NewVal]=n.value,[OldVal]=o.value,[DiffType]=3
	from newdata n
	inner join olddata o on n.[KeyVal]=o.[KeyVal] and n.[Key]=o.[Key]
	where isnull(n.value,'''')<>isnull(o.value,'''')

	merge PdDiffDataS tg
	using #tempPdDiffDataS su
	on tg.SampSerial=su.SampSerial and tg.CompareId=su.CompareId and tg.KeyVal=su.KeyVal and tg.ColCode=su.ColCode
	WHEN NOT MATCHED then
		insert ([AccId],[SampSerial],[CompareId],[KeyVal],[ColCode],[NewVal],[OldVal],[DiffType],[UpdaterCode],[UpdateDate])
		values (su.[AccId],su.[SampSerial],su.[CompareId],su.[KeyVal],su.[ColCode],su.[NewVal],su.[OldVal],su.[DiffType],@User,@DateTime)
	WHEN MATCHED and su.[NewVal]<>tg.[NewVal] then
		update set tg.[NewVal]=su.[NewVal],[UpdaterCode]=@User,[UpdateDate]=@DateTime;
	;
	--之前更新过，现在又更新回来
	delete a
	from PdDiffDataS a
	left join #tempPdDiffDataS b on b.SampSerial=a.SampSerial and b.CompareId=a.CompareId and b.KeyVal=a.KeyVal and b.ColCode=a.ColCode
	where a.SampSerial=@SampSerial and a.CompareId=@CompareId and a.KeyVal in(select KeyVal from #newdata where isdelete=0) and b.SampSerial is null;

end try
begin catch
	set @Err=ERROR_MESSAGE()
end catch

'
--print @sql
exec sp_executesql @sql,N'@SampSerial varchar(50),@CompareId int,@AccId smallint,@User varchar(50),@DateTime datetime,@Err nvarchar(2000) out'
	,@AccId=@AccId,@SampSerial=@SampSerial,@CompareId=@CompareId,@User=@User,@DateTime=@DateTime,@Err=@Err out
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AccId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'AccId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CompareId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'CompareId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CompareTab' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'CompareTab'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CompareDesc' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'CompareDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OrgTab' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'OrgTab'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow5'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow6'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow7'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsShow8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsShow8'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'OrderId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Remark' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'Remark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'Creator'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'CreatorCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'Updater'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'UpdaterCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'IsDelete'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'OperDept'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH', @level2type=N'COLUMN',@level2name=N'RoCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'板单比较设置主表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareH'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AccId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'AccId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CompareId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'CompareId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ColCode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'ColCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ColName' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'ColName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsKey' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsKey'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IsCompare' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsCompare'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow5'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow6'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow7'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Show8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsShow8'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'Creator'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'CreatorCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'Updater'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'UpdaterCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'IsDelete'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'OperDept'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS', @level2type=N'COLUMN',@level2name=N'RoCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'板单比较设置子表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdCompareS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AccId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataH', @level2type=N'COLUMN',@level2name=N'AccId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'板单流水号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataH', @level2type=N'COLUMN',@level2name=N'SampSerial'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'原板单流水号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataH', @level2type=N'COLUMN',@level2name=N'SampSerialOrg'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'板单差异主表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataH'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AccId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'AccId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'板单流水号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'SampSerial'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CompareId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'CompareId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'KeyVal'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ColCode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'ColCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'NewVal' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'NewVal'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OldVal' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'OldVal'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'DiffType' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'DiffType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'UpdaterCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PdDiffDataS', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO

 

大家如还有其它方法，欢迎你发表！

Dear SQL(燊)
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java实现的进销存管理软件
12-03
在这个特定的"Java实现的进销存管理软件"中，开发人员利用了Java的强大功能和灵活性，构建了一个与SQL Server 2012数据库相结合的系统，为用户提供了一整套全面的库存管理和交易处理解决方案。 首先，Java作为编程...
用友U8_EAI数据接口常见问题.doc
10-12
- **解决方案**：可以通过SQL Server查询分析器获取U8系统的数据库时间戳，并将其作为参考点来筛选数据。具体步骤如下： - 连接到U8的帐套数据库（如`UFDATA_001_2009`）。 - 执行以下SQL命令：`SELECT @@DBTS`。 ...
SQL Server 各种时间业务处理
weixin_33908217的博客
11-21 117
1.SQL 生成随机字符串作为流水号 SELECT CONVERT(varchar(100), GETDATE(), 112) --年月日时分秒 SELECT 'JS'+replace(CONVERT(varchar(10), GETDATE(), 112),'-','') +replace(CONVERT(varchar(10), GETDATE(), 108),':','...
同一接口被多次请求，数据还未来得及入库导致数据不一致的问题
qq_42203603的博客
06-02 1796
有一个接口，使用了事务，而且会自建数据，但是只能允许自建一种类型的数据。如果请求分开来，一次结束后接着第二次，那么就会如期只自建一个。但如果请求同时发起，同一种类型的就会出现多条自建数据。在因为有个事务的原因，事务会完成之后再写入到数据库。这时候如果当前事务还没完成的时候，就进行第二次请求，就会导致查数据库的时候没有自建数据，然后这次请求也进行了自建，这样就导致了只允许自建一次的数据自建了两次。开发中经常会遇到这种问题，主要核心在事务身上。事务注解里面有个值可以设定事务隔离级别，将事务隔离级别设为可以读取其
从一笔金币充值去思考分布式事务
java面试笔试
12-09 263
文章纲要此次分享的缘由目前分布式事务问题是怎么解决的行业中有什么解决方案这些解决方案分别有什么优缺点别人是怎么做的我们可以怎么来做此次分享的缘由支付重构考虑支付重构的时候...
2020年Java面试题
zhangzhen02的博客
08-27 232
并行流底层实现和问题 底层使用forkjoinpool,默认线程数为核心数，拆分使用对半递归拆分。 问题，1.同用一个池子，线程数有限，如果有IO阻塞任务，将会导致其他任务长时间等待。 sql执行流程 连接-查询缓存-语法解析器和预处理-查询优化器-执行计划-执行SQL 连接：tcp连接，权限验证 查询缓存：根据query进行hash运算，存储结果 语法解析器和预处理：验证语法是否正确，库表字段是否存在 查询优化器：基于成本选择成本最小的语句 执行计划：生成查询的指令树 执行sql: 调用存储引擎的API进
物流管理系统财务子系统的设计与实现论文.doc
09-21
同时，为了实现数据的实时同步，可能还运用了消息队列（MQ）或其他中间件技术，确保物流活动和财务信息的即时更新。 总的来说，这篇论文详细探讨了物流管理系统财务子系统的设计与实现过程，为物流行业的信息化建设...
如何快速满足客户的差异化需求.pptx
10-14
通过B/S（Browser/Server）模式，RAP使得普通用户无需编写代码就能实现档案、单据和报表的定制，实现真正的“所见即所得”。 **RAP的竞争优势** 1. **市场产品稳定性**：由于基于T+架构，RAP保证了产品的稳定性和...
AppFramework数据库访问组件_代码生成插件_V1.1.rar
01-06
除了实现数据的增删改查，数据访问层还要提供一些与业务无关功能，例如面向对象的持久化与访问机制、本地事务与分布式事务支持、多数据库支持，这些机制或功能形成相对独立的逻辑领域，其主要目的有： <br>1、 ...
进销存设计之——单据的数据表结构设计
03-03 2226
单据的数据表设计 在表设计中，我尝试过两种方法：把销售单、采购单等等单据用同一个实体表示，然后通过单据类型的字段来标识，因为单据设计到商品的出入，所以单据类型必须有出入状态标志。这样做的理由是：1、符合OO的设计思想，把订单做为一个类2、代码重用性好，效率高3、有利于业务扩展，通过对订单类继承，增加接口方法就可以增加新的业务在实现过程中，特别是数据库设计中，这种思想产生了一些很小的表 
JAVA实现生成单据编号和根据时间段计算相隔天数
LEN程序员的一生
11-12 523
/** * @author lenian 2018 10 17 * @deprecated 审批-单据编号 * @text eg:sp-20180821-0005 * @param date * @return */ public static String voucherNumber() { SimpleDateFormat sdf = new SimpleDateFormat(...
单据数据结构设计
weixin_30376323的博客
07-21 593
单据数据结构设计 单据形式 企业中的表单（请假单、加班单等等）大多数完成两个功能：一、审批；二、产生业务记录。一般情况下，会根据表单上字段所处的位置，将其设计为单据头、单据体那样一对多的关系。通一个业务可能表单的形式不尽相同，以加班单为例：有一人多天加班、有多人一天加班，有多人多天的加班。不论怎么变化，只是数据的表现形式不一样而已，只有遵循一定的设计方法才能应付这种变化 ...
面试刷题10-3
wwxy1995的博客
10-03 7621
Java 面试重点内容 其实最大的感受是，Java并发编程太重要了，几乎每场面试，无论大小公司，都会问到多线程、线程池、synchronized、各种锁等等这些东西；问的第二多的是HashMap、ConcurrentHashMap、TreeMap、ArrayList等这些集合框架；然后就是JVM了，内存结构、垃圾回收算法、垃圾收集器、如何调优等等。感觉在Java方面如果把《深入理解Java虚拟机...
手把手教你搞定 微信支付 跳出微信支付的坑 （公众号支付，核心代码可以用于小程序支付）
热门推荐
请叫我书生的博客
10-19 2万+
1. 准备工作 设置支付目录 请确保实际支付时的请求目录与后台配置的目录一致，否则将无法成功唤起微信支付。 在微信商户平台（pay.weixin.qq.com）设置您的公众号支付支付目录，设置路径：商户平台-->产品中心-->开发配置，如下图所示。公众号支付在请求支付的时候会校验请求来源是否有在商户平台做了配置，所以必须确保支付目录已经正确的被配置，否则将验证失败，请...
请求返回数据和浏览器显示数据不一致
torrytang的专栏
01-17 4116
服务器返回的数据，浏览器通过解析后可能出现差异
SQL Server 2012各版本功能深度解析：试用期与差异对比
本文档详细比较了SQL Server 2012的不同版本在功能上的异同，主要关注于企业级、标准版、商业智能版、Web版以及Express版本。以下是关键知识点的总结： 1. **版本分类**: - Enterprise版：这是最高级别的版本，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值