利用一个存储过程
| 代码如下 | 复制代码 |
| ALTER PROC NumShowTimes ( @numValue NVARCHAR(MAX) ) AS BEGIN DECLARE @TempTable TABLE(num NVARCHAR(4))---定义存放数字的表 DECLARE @TempNum NVARCHAR(4) -- DECLARE @Value nvarchar(max) SET @Value = @numValue ---判断输入的是否是数字 WHILE(LEN(@numValue)>0) BEGIN SET @TempNum=SUBSTRING(@numValue,LEN(@numValue),1) SET @numValue=SUBSTRING(@numValue,1,(LEN(@numValue)-1)) IF @TempNum LIKE'[^0-9]' BEGIN PRINT 'YOU INPUT NOT NUM' RETURN END END ----获取数据插入表中 WHILE(LEN(@Value)>0) BEGIN SET @TempNum=SUBSTRING(@Value,LEN(@Value),1) SET @Value=SUBSTRING(@Value,1,(LEN(@Value)-1)) INSERT INTO @TempTable VALUES (@TempNum) END SELECT num,count(num)AS [count] FROM @TempTable GROUP BY num END |
|
去掉判断输入的是否是数字那个while循环，就显示所输入的所字符出现的次数，包括汉字
SQL利用Case When Then Else End 多条件判断 以指定的次数重复字符 （生成当天最大顺序编号）
| 代码如下 | 复制代码 |
|
Select Case When a is not null then a When b is not null then b When c is not null then c When d is not null then d Else '' End 列名 From Table Name |
|
SQL利用Case When Then多条件判断
| 代码如下 | 复制代码 |
|
Select top 100 State,JoinState, (case when State=1 and Joinstate=0 then 2 when State=1 and JoinState=1 then 1 else 0 end ) as usestate from UserInfo （生成当天最大顺序编号） select substring(convert(varchar(8), getdate(), 112), 1, 8) +'568' + replicate('0', 4-len(max_ContractNo)) --以指定的次数重复字符表达式 + cast(max_ContractNo as varchar(4)) from (select case when substring(max(My_ContractNo), 12, 4) is null then '1' else cast(cast(substring(max(My_ContractNo), 12, 4) as bigint) + 1 as varchar(8)) end max_ContractNo from UserContractNo WHERE datediff(Day,Adddate,getdate())=0 ) s CREATE TABLE [UserContractNo]( [Id] [int] IDENTITY(1,1) NOT NULL, [ApplyUserDetailID] [int] NOT NULL, [My_ContractNo] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_UserContractNo_My_ContractNo] DEFAULT (''), [AddDate] [datetime] NOT NULL CONSTRAINT [DF_UserContractNo_AddDate] DEFAULT (getdate()), [Remarks] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_UserContractNo_Remarks] DEFAULT (''), CONSTRAINT [PK_UserContractNo] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY] ) ON [PRIMARY] |
|