--去空格 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')
DROP TABLE lOG_NOTE
CREATE TABLE lOG_NOTE(
ID [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
INTERFACE [VARCHAR](50) NOT NULL,
URL [VARCHAR](50) NOT NULL,
REQUEST [NVARCHAR](MAX) NOT NULL,
REQUEST_TIME [DATETIME] NOT NULL,
LENGTH_TIME [BIGINT] NOT NULL,
STATUS [NVARCHAR](10) NOT NULL,
REPONSE [NVARCHAR](MAX) NOT NULL,
IP [VARCHAR](30) NOT NULL,
ERROR [NVARCHAR](MAX)
)
EXECUTE sp_addextendedproperty N'MS_Description','接口日志记录表',N'user',N'dbo',N'table',N'lOG_NOTE',NULL,NULL
EXECUTE sp_addextendedproperty N'MS_Description','自增ID',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'ID'
EXECUTE sp_addextendedproperty N'MS_Description','接口',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'INTERFACE'
EXECUTE sp_addextendedproperty N'MS_Description','请求地址',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'URL'
EXECUTE sp_addextendedproperty N'MS_Description','请求内容',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'REQUEST'
EXECUTE sp_addextendedproperty N'MS_Description','请求时间',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'REQUEST_TIME'
EXECUTE sp_addextendedproperty N'MS_Description','消耗时长(MS)',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'LENGTH_TIME'
EXECUTE sp_addextendedproperty N'MS_Description','状态',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'STATUS'
EXECUTE sp_addextendedproperty N'MS_Description','响应内容',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'REPONSE'
EXECUTE sp_addextendedproperty N'MS_Description','服务执行地址',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'IP'
EXECUTE sp_addextendedproperty N'MS_Description','异常信息',N'user',N'dbo',N'table',N'lOG_NOTE',N'column',N'ERROR'
-- 创建复合索引
CREATE INDEX IX_LOG_INTERFACE_TIME
ON lOG_NOTE(INTERFACE,REQUEST_TIME)
行转列
CREATE TABLE [dbo].[TARGET_CONFIG]
(
[PROJ_CODE] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PRODUCT_CODE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PRODUCT_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[PRODUCT_SEQ] [int] NULL,
[PROCESS_ID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PROCESS_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[PROCESS_SEQ] [int] NULL,
[PROCESS_DESC] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[PROCESS_TYPE] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL,
[TARGET_VALUE] [decimal] (10, 2) NULL,
[TARGET_TIME] [date] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TARGET_CONFIG] ADD CONSTRAINT [PK_TARGET_CONFIG] PRIMARY KEY CLUSTERED ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [TARGET_TIME]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PROJ_CODE] ON [dbo].[TARGET_CONFIG] ([PROJ_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PRODUCT_CODE] ON [dbo].[TARGET_CONFIG] ([PRODUCT_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PROCESS_ID] ON [dbo].[TARGET_CONFIG] ([PROCESS_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_TARGET_TIME] ON [dbo].[TARGET_CONFIG] ([TARGET_TIME]) ON [PRIMARY]
GO
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 8553.00, '2023-10-01');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 8553.00, '2023-10-02');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 8553.00, '2023-10-03');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 8553.00, '2023-10-04');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 8553.00, '2023-10-05');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-06');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-07');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-08');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-09');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-10');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-11');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-12');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-13');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-14');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-15');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-16');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-17');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-18');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-19');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-20');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-21');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-22');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-23');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-24');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-25');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-26');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-27');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-28');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-29');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-30');
INSERT INTO DASHBOARD.dbo.TARGET_CONFIG
(PROJ_CODE, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_SEQ, PROCESS_ID, PROCESS_NAME, PROCESS_SEQ, PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME)
VALUES(N'NCCSAP', N'LDU1_ADV2', N'ALDAN_V2_LDU1', 1, N'LD_BOND_SUBMOUNT', N'LD_BOND_SUBMOUNT', 2, N'LD_BOND_PROD', N'ASSEMBLY', 9540.00, '2023-10-31');
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- 获取所有不重复的TARGET_TIME值
SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(TARGET_TIME)
FROM TARGET_CONFIG WHERE PROJ_CODE='NCCSAP' AND MONTH(TARGET_TIME) = MONTH(GETDATE()) AND YEAR(TARGET_TIME) = YEAR(GETDATE())
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
-- 构建动态SQL查询语句
SET @sql = N'
SELECT PROCESS_DESC, PROCESS_TYPE, ' + @columns + '
FROM
(
SELECT PROCESS_DESC, PROCESS_TYPE, TARGET_VALUE, TARGET_TIME
FROM TARGET_CONFIG
) AS SourceTable
PIVOT
(
MAX(TARGET_VALUE)
FOR TARGET_TIME IN (' + @columns + ')
) AS PivotTable;';
-- 执行动态SQL查询
EXEC sp_executesql @sql;
SQL Server排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）
1、 ROW_NUMBER
Row_number的用途非常广泛，排序最好用他，一般可以用来实现web分页，他会为查询出来的每一页记录生成一个序号，依次排序且不重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
例如：
select ROW_NUMBER() OVER(order by salse desc) as row_num,* from rank1
over子句中根据datatime降序排列，Sql语句则按salse降序排列
例如：
select ROW_NUMBER() OVER(order by [datatime] desc) as row_num,* from rank1 order by [salse] desc
利用row_number可以实现web程序分页，我们来查询指定范围的表数据。
例如：
with orderSection as
(
select ROW_NUMBER() OVER(order by salse desc) rownum,* from rank1
)
select * from [orderSection] where rownum between 5 and 8 order by salse desc
下面我们写一个例子来证实这一点，将上面Sql语句中的排序字段由salse改为datatime。
例如：
with orderSection as
(
select ROW_NUMBER() OVER(order by salse desc) rownum,* from rank1
)
select * from [orderSection] where rownum between 5 and 8 order by datatime desc
2、 RANK
rank函数用于返回结果集的分区内每行的排名， 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。
例如：
select RANK() OVER(order by [salse]) as rank,* from rank1
3、DENSE_RANK
dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现
例如：
select DENSE_RANK() OVER(order by [salse]) as den_rank,* from rank1
4、NTILE
ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。
例如：
select NTILE(4) OVER(order by salse desc) as ntile,* from rank1
总结：
在使用排名函数的时候需要注意以下三点：
1、排名函数必须有 OVER 子句。
2、排名函数必须有包含 ORDER BY 的 OVER 子句。
3、分组内从1开始排序。
Sqlsever 动态行转列
--表数据
CREATE TABLE #temptable ( [PROJ_CODE] varchar(50), [EQUIPMENT_GROUP] varchar(50), [TESTER] varchar(50), [OEE] decimal(38,4), [DATE] date )
INSERT INTO #temptable
VALUES
( 'NCCSAD', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.8928, N'2024-03-02T00:00:00' ),
( 'NCCSAD', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.8116, N'2024-03-01T00:00:00' ),
( 'NCCSAP', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.0255, N'2024-03-01T00:00:00' )
--DROP TABLE #temptable
DATE 多行
实现方式一
--动态的列名
DECLARE @cols AS NVARCHAR(MAX);
--拼接 sql
DECLARE @query AS NVARCHAR(MAX);
--拼接动态列名
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(DATE)
FROM #temptable
WHERE DATE >= '2024-03-01' AND DATE <= '2024-03-31'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
PRINT @cols
--拼接sql
SET @query = 'SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ' + @cols + '
FROM
(
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, OEE, DATE
FROM #temptable
WHERE DATE >= ''2024-03-01'' AND DATE <= ''2024-03-31''
) x
PIVOT
(
MAX(OEE)
FOR DATE IN (' + @cols + ')
) p ';
EXECUTE(@query);
实现方式二
--动态的列名
DECLARE @cols NVARCHAR(MAX) = '';
--拼接 sql
DECLARE @query NVARCHAR(MAX);
SELECT @cols += QUOTENAME(DATE) + N','
FROM #temptable
WHERE DATE >= '2024-03-01'
AND DATE <= '2024-03-31'
GROUP BY DATE
ORDER BY DATE;
PRINT @cols
--去掉最后一个 逗号
SET @cols = LEFT(@cols, LEN(@cols) - 1);
--拼接sql
SET @query = '
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ' + @cols + '
FROM
(
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, OEE, DATE
FROM #temptable
WHERE DATE >= ''2024-03-01'' AND DATE <= ''2024-03-31''
AND TESTER =''FLIP_TOSA_AA_34''
) x
PIVOT
(
MAX(OEE)
FOR DATE IN (' + @cols + ')
) p ';
EXECUTE(@query);
以上有一个问题，就是数据源根本没有 相关日期的数据，那么转换后显示的是0，
需要再加一列
--动态的列名
DECLARE @cols NVARCHAR(MAX) = '';
DECLARE @cols1 NVARCHAR(MAX) = '';
--拼接 sql
DECLARE @query NVARCHAR(MAX);
SELECT @cols += 'ISNULL(' + QUOTENAME(DATE) + ', 0) AS ' + QUOTENAME(DATE) + ','
FROM #temptable
WHERE DATE >= '2024-03-01'
AND DATE <= '2024-03-31'
GROUP BY DATE
ORDER BY DATE;
PRINT @cols
SET @cols = LEFT(@cols, LEN(@cols) - 1);
SELECT @cols1 += QUOTENAME(DATE) + N','
FROM #temptable
WHERE DATE >= '2024-03-01'
AND DATE <= '2024-03-31'
GROUP BY DATE
ORDER BY DATE;
PRINT @cols1
SET @cols1 = LEFT(@cols1, LEN(@cols1) - 1);
--去掉最后一个 逗号
--拼接sql
SET @query = '
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ' + @cols + '
FROM
(
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ISNULL(OEE, 0) AS OEE, DATE
FROM #temptable
WHERE DATE >= ''2024-03-01'' AND DATE <= ''2024-03-31''
AND TESTER =''FLIP_TOSA_AA_34''
) x
PIVOT
(
MAX(OEE)
FOR DATE IN ('+@cols1+')
) p ';
EXECUTE(@query);
DROP TABLE #temptable;
CREATE TABLE #temptable
(
[PROJ_CODE] VARCHAR(50),
[EQUIPMENT_GROUP] VARCHAR(50),
[TESTER] VARCHAR(50),
[OEE] DECIMAL(38, 4),
[DATE] VARCHAR(10)
);
INSERT INTO #temptable
VALUES
('NCCSAD', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.8928, '2024-03-02'),
('NCCSAD', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.8116, '2024-03-01'),
('NCCSAP', 'FLIP_TOSA_AA', 'FLIP_TOSA_AA_34', 0.0255, '2024-03-01');
--DROP TABLE #temptable
SELECT * FROM #temptable WHERE TESTER ='FLIP_TOSA_AA_34'
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @valuekey AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX);
SELECT @valuekey = STUFF(
(
SELECT N',ISNULL(['+[DATE]+'], 0) AS '+ QUOTENAME([DATE])
FROM #temptable
GROUP BY [DATE]
ORDER BY [DATE]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,''),
@cols=STUFF(
(
SELECT ',' + QUOTENAME([DATE])FROM #temptable
GROUP BY [DATE]
ORDER BY [DATE]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'');
--PRINT @valuekey;
--PRINT @cols;
--拼接sql
SET @query = '
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ' + @valuekey + '
FROM
(
SELECT PROJ_CODE, EQUIPMENT_GROUP, TESTER, ISNULL(OEE, 0) AS OEE, DATE
FROM #temptable
WHERE DATE >= ''2024-03-01'' AND DATE <= ''2024-03-31''
) x
PIVOT
(
MAX(OEE)
FOR DATE IN (' + @cols + ')
) p ';
PRINT @query
EXECUTE(@query);
扫一扫
1414
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
