在项目开发过程中,遇到数据库的查询问题
select * from syscolumns SELECT object_id('TB_KYChildProject') select * from syscolumns where id=object_id('TB_KYChildProject') and name='IsInheritAdd'
go if exists(select 1 from sys.extended_properties p where p.major_id = object_id('TB_KYChildProject') and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ChildProjectManageName') ) begin declare @CurrentUser sysname select @CurrentUser = user_name() execute sp_dropextendedproperty 'MS_Description', 'user', @CurrentUser, 'table', 'TB_KYChildProject', 'column', 'ChildProjectManageName' end select @CurrentUser = user_name() execute sp_addextendedproperty 'MS_Description', '子项目经理名称', 'user', @CurrentUser, 'table', 'TB_KYChildProject', 'column', 'ChildProjectManageName' go
TB_KYChildProject是表名,ChildProjectManageName字段名,子项目经理名称是字段说明
USE [BJSkyTwo_Dev] GO /****** Object: StoredProcedure [dbo].[pCreateInsertScript] Script Date: 10/31/2017 15:24:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --@tablename 表名,@con 条件 --EXEC pCreateInsertScript @tablename = 'Find01_FindSetting',@con = 'Find01Find00ID =58' ALTER proc [dbo].[pCreateInsertScript] (@tablename varchar(256),@IsIncludeIdentityColumn BIT=0,@con varchar(MAX)) as BEGIN DECLARE @ID INT SET @ID=object_id(@tablename) declare @sql varchar(MAX) declare @sqlValues varchar(MAX) set @sql =' (' set @sqlValues = 'values (''+' select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],' from (select case when xtype in (48,52,56,59,60,62,104,106,108,122,127) then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end' when xtype in (58,61) then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end' when xtype in (167) then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' when xtype in (35) then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ 'cast('+ name + ' as varchar)'+','''''''','''''''''''')' + '+'''''''''+' end' when xtype in (231) then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' when xtype in (175) then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end' when xtype in (239) then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end' --when xtype in (36) ELSE 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' --else '''NULL''' end as Cols,name from syscolumns where id = @ID AND 1=(CASE WHEN @IsIncludeIdentityColumn=0 AND COLUMNPROPERTY(@ID,[name], 'IsIdentity') = 1 THEN 0 ELSE 1 END) ) T set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename+' where 1=1 and '+@con print @sql exec (@sql) end
用法:
dbo.pCreateInsertScript @tablename = 'System_Menu', -- varchar(256) @IsIncludeIdentityColumn = 0, -- bit @con = 'MenuName = ''监理任务派单''' -- varchar(max)
存在的问题:
PRINT只能打印4000的Unicode string或8000的Non-unicode string,过长会导致字符串被截断
转换成
SELECT a.userID , STUFF(( SELECT ',' + System_Role.RoleName FROM System_Role WHERE CHARINDEX(',' + CONVERT(VARCHAR(36), System_Role.RoleID) + ',', ',' + a.RoleIDs + ',') > 0 ORDER BY System_Role.RoleID FOR XML PATH('') ), 1, 1, '') AS RoleNames FROM System_Users a
WITH mt AS ( SELECT a.UserID , b.RoleID , b.RoleName FROM System_Users a , System_Role b WHERE ',' + a.RoleIDs + ',' LIKE '%,' + CONVERT(VARCHAR(36), b.RoleID) + ',%' ) SELECT UserID , STUFF(( SELECT ',' + RoleName FROM mt a WHERE a.UserID = mt.UserID FOR XML PATH('') ), 1, 1, '') RoleNames FROM mt GROUP BY UserID
SELECT SUM(CASE WHEN a.Sub_Project_Type =101 THEN 1 ELSE 0 END)*1.0 / COUNT(*) 类型比例 FROM TB_KYSubProject a
效果:
SELECT a.* , STUFF(( SELECT ',' + CONVERT(VARCHAR(100), b.RoleMajorType) FROM System_Role b WHERE CHARINDEX(',' + CONVERT(VARCHAR(36), b.RoleID) + ',', ',' + a.RoleIDs + ',') > 0 GROUP BY b.RoleMajorType FOR XML PATH('') ), 1, 1, '') AS RoleMajorType FROM System_Users a
效果:
SELECT a.* , STUFF(( SELECT DISTINCT ( ',' + CONVERT(NVARCHAR(10), r.RoleMajorType) ) FROM ( SELECT a.UserID , b.RoleID FROM System_Users a CROSS APPLY ( SELECT CAST(col AS UNIQUEIDENTIFIER) RoleID FROM fn_split(RoleIDs, ',') ) b ) ru LEFT JOIN dbo.System_Role r ON r.RoleID = ru.RoleID WHERE ru.UserID = a.UserID FOR XML PATH('') ), 1, 1, '') AS RoleMajorType FROM System_Users a WHERE 1 = 1
效果:
1|216|368|截取成1|216|,1|2112|3683|21|截取成1|2112|
推荐
select LEFT('1|216|368|',CHARINDEX ('|', '1|216|368|' , CHARINDEX ('|', '1|216|368|')+1))
不推荐
select LEFT('1|216|368|',charindex('|',stuff('1|216|368|',charindex('|','1|216|368|'),1,',')))