--去空格 TRIM()
SELECT LTRIM(RTRIM(N'去空格 '))
LTRIM --去左空格
RTRIM --去右空格
--判断值类型
SELECT ISDATE('20200601') --判断是否是日期 1=是;0=否
SELECT ISNUMERIC('-19555.22') --判断是否是数字(包含小数，负数) 1=是;0=否
--@@IDENTITY @@IDENTITY得到上一次插入记录时自动产生的ID
SELECT @@IDENTITY as Template_ID
--NEWID()生成计算机唯一不重复编码
SELECT NEWID() --多用于礼券码和唯一数据主键
--字符替换
SELECT REPLACE('2020/01-01','/', '-')
--1、round()遵循四舍五入把原值转化为指定小数位数。
--如：round(1.45,0) = 1;round(1.55,0)=2
SELECT ROUND(1.45, 0) AS ROUNDT
--2、CAST(159.22 AS INT)转整数去掉后面小数
SELECT CAST(159.22 AS INT) AS CASTE
ROUNDT
---------------------------------------
1.00
(1 行受影响)
CASTE
-----------
159
(1 行受影响)
--FULL OUTER JOIN 是针对 LEFT JOIN、INNER JOIN、 RIGHT JOIN 的缺陷
--FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
--LEFT JOIN、INNER JOIN 的缺陷必须匹配主表(目标表)才能关联数据如没有匹配到就不会显示
--UNION 也只能合并相同表的结果集 场景不适用
CREATE TABLE #USER_A
(
Branch_Code INT NOT NULL ,
Month_Date DATE NOT NULL ,
Resales_Money_Target MONEY NOT NULL ,
Create_By VARCHAR(50) NOT NULL ,
Create_Time DATETIME2 NOT NULL ,
Update_By VARCHAR(50) NOT NULL ,
Update_Time DATETIME2 NOT NULL ,
Approval_By VARCHAR(50) NOT NULL ,
Approval_Flag SMALLINT NOT NULL ,
Approval_Remark NVARCHAR(200) NOT NULL
)
SELECT *FROM #USER_A
Branch_Code Month_Date Resales_Money_Target Create_By Create_Time Update_By Update_Time Approval_By Approval_Flag Approval_Remark
----------- ---------- --------------------- -------------------------------------------------- --------------------------- -------------------------------------------------- --------------------------- -------------------------------------------------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3379 2020-04-01 397164.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版)
3379 2020-05-01 476597.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版)
3379 2020-06-01 365391.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版)
3379 2020-07-01 381277.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版)
3379 2020-08-01 593098.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版)
(5 行受影响)
CREATE TABLE #INFOR_B
(
[Branch_Code] [int] NOT NULL,
[Month_Date] [date] NOT NULL,
[Resales_Money] [money] NOT NULL
)
SELECT *FROM #INFOR_B
Branch_Code Month_Date Resales_Money
----------- ---------- ---------------------
3113 2020-05-01 269565.90
3113 2020-06-01 756102.20
3113 2020-07-01 37134.80
3145 2020-05-01 2080761.48
3145 2020-06-01 2590597.88
3145 2020-07-01 137244.17
3146 2020-05-01 560768.30
3146 2020-06-01 752322.66
3146 2020-07-01 7574.50
3154 2020-05-01 492991.00
(10 行受影响)
SELECT *
FROM #USER_A AS RRMT
FULL OUTER JOIN #INFOR_B AS RRM
ON RRM.Branch_Code = RRMT.Branch_Code
AND RRM.Month_Date = RRMT.Month_Date
Branch_Code Month_Date Resales_Money_Target Create_By Create_Time Update_By Update_Time Approval_By Approval_Flag Approval_Remark Branch_Code Month_Date Resales_Money
----------- ---------- --------------------- -------------------------------------------------- --------------------------- -------------------------------------------------- --------------------------- -------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ---------------------
3379 2020-04-01 397164.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版) NULL NULL NULL
3379 2020-05-01 476597.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版) NULL NULL NULL
3379 2020-06-01 365391.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版) NULL NULL NULL
3379 2020-07-01 381277.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版) NULL NULL NULL
3379 2020-08-01 593098.00 10039069 2020-06-29 20:05:05.4970000 10039069 2020-06-29 20:05:05.4970000 10039069 1 0629批量导入西北区分店再消费目标(6月初版) NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3113 2020-05-01 269565.90
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3113 2020-06-01 756102.20
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3113 2020-07-01 37134.80
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3145 2020-05-01 2080761.48
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3145 2020-06-01 2590597.88
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3145 2020-07-01 137244.17
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3146 2020-05-01 560768.30
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3146 2020-06-01 752322.66
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3146 2020-07-01 7574.50
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3154 2020-05-01 492991.00
(15 行受影响)
--添加字段
ALTER TABLE 表名
ADD 字段名字 NVARCHAR(200) DEFAULT NULL;
EXECUTE sp_addextendedproperty 'MS_Description', '字段注释', 'user', 'dbo',
'table', '表名', 'column', '字段名';
CREATE TABLE [dbo].[EXTERNALURL](
[TXN_CODE] [VARCHAR](50) NOT NULL,
[MEANING_EN] [VARCHAR](50) NOT NULL,
[MEANING_ZH] [VARCHAR](50) NULL,
[EXTERNAL_URL] [NVARCHAR](200) NULL,
CONSTRAINT [PK_SY_S_EXTERNALURL] PRIMARY KEY CLUSTERED
(
[TXN_CODE] 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'EXTERNALURL', @level2type=N'COLUMN',@level2name=N'TXN_CODE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'程序名称EN' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EXTERNALURL', @level2type=N'COLUMN',@level2name=N'MEANING_EN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'程序名称ZH' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EXTERNALURL', @level2type=N'COLUMN',@level2name=N'MEANING_ZH'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'程序URL 不带ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EXTERNALURL', @level2type=N'COLUMN',@level2name=N'EXTERNAL_URL'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外部链接表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EXTERNALURL'
GO
--循环一张表的数据
DECLARE @COUNTCODE INT;--总行数
DECLARE @TXN_CODE VARCHAR(30);
DECLARE @SUB_SYSTEM NVARCHAR(50)= 'RESOURCETRACKING';
DECLARE @RELATIVE_URL NVARCHAR(200)= '/ResourceTracking/ExternalUrl/index';
DECLARE @MODULE_CODE VARCHAR(30)= '01. Resource Tracking';
DECLARE @CREATOR VARCHAR(25)= 'danqiyang';
DECLARE @TXN_GROUP NVARCHAR(200)= 'ADMIN_TXN,IT_USER_TXN';
DECLARE @MEANING_EN VARCHAR(200);
DECLARE @MEANING_ZH NVARCHAR(200);
DECLARE @EXTERNAL_URL NVARCHAR(200);
--加索引
IF OBJECT_ID('tempdb..#Tpme') IS NOT NULL
DROP TABLE #Tpme;
SELECT ROW_NUMBER() OVER ( ORDER BY TXN_CODE ) - 1 RN ,
*
INTO #Tpme
FROM [EXTERNALURL];
SELECT @COUNTCODE = COUNT([TXN_CODE]) FROM [EXTERNALURL];
DECLARE @i INT = 0;
WHILE @i < @COUNTCODE
BEGIN
SELECT @TXN_CODE = TXN_CODE ,
@MEANING_EN = MEANING_EN ,
@MEANING_ZH = MEANING_ZH ,
@EXTERNAL_URL = EXTERNAL_URL
FROM #Tpme
WHERE RN = @i;
--循环执行SP
EXEC dbo.SP_IT_TOOLS_CREATE_MENU @TXN_CODE, @SUB_SYSTEM, @RELATIVE_URL,
@MODULE_CODE, @CREATOR, @TXN_GROUP, @MEANING_EN, @MEANING_ZH,
@EXTERNAL_URL;
--PRINT ( @MEANING_EN );
SET @i = @i + 1;
END;
--查询包含某字段的所有表
SELECT OBJECT_NAME(id) objName ,
name AS colName
FROM syscolumns
WHERE ( name LIKE '%OPERATION%' )
AND id IN ( SELECT id
FROM sysobjects
WHERE xtype = 'u' )
ORDER BY objName;
--当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'table_name' 中的标识列插入显式值。
--执行插入语句，报错。
--“当 IDENTITY_INSERT 设置为 OFF 时，不能为表 ‘orders’ 中的标识列插入显式值。”
先运行“SET IDENTITY_INSERT 表名 ON”，再运行你的插入语句，再执行“SET IDENTITY_INSERT 表名 OFF”就可以了。
例如：
SET IDENTITY_INSERT OrderList ON–打开
SET IDENTITY_INSERT OrderList OFF–关闭
保留两位小数，带0
ISNULL(CAST(Ith_25 AS DECIMAL(9, 2)), '0.00')
网友评论