随笔分类 - 数据库
摘要：SQL联查更新 update t_caliper_report_param set t_caliper_report_param.dep_id = t_dep_project.dep_id from t_caliper_report_param,t_dep_project where t_calip
阅读全文
摘要：update t_sample set is_std = iif(CHARINDEX('std',number)>0,1,0), is_blank = iif(CHARINDEX('blank',number)>0,1,0)
阅读全文
摘要：SELECT CONVERT(INT,value) FROM STRING_SPLIT(CONVERT(VARCHAR(MAX),'1,2,3,4,5,6,7,8,9'),',')
阅读全文
摘要：1、加索引，只能针对百万左右数据量 2、分表，每个月的数据归档到一个新表按月保存，查询时使用union连查
阅读全文
摘要：SELECT * FROM (SELECT tt.*, ROWNUM AS rowno FROM ( SELECT t.vin_mt ,dbxx FROM {PdaCommonAppService.PREFIX}v_pj_dygx t WHERE vin_jj like '%{input.vin_j
阅读全文
摘要：select rand(CHECKSUM(NEWID()))
阅读全文
摘要：SELECT TableName = CASE WHEN A.colorder = 1 THEN D.name ELSE '' END, PrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND p
阅读全文
摘要：SELECT TableName = CASE WHEN A.colorder = 1 THEN D.name ELSE '' END, PrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND p
阅读全文
摘要：单主键 with T as( SELECT ROW_NUMBER() over (PARTITION By 工件号 order by 检测时间 desc) as rowid,* FROM 检测表 ) delete from T where id not in( select id from T wh
阅读全文
摘要：select msd.*,sc.* FROM MachinesShiftDetails as msd OUTER APPLY (select top 1 * from ShiftCalendars where MachineShiftDetailId = msd.Id) as sc
阅读全文
摘要：with TS as( SELECT ROW_NUMBER() over (PARTITION By code order by code) as rowid,* FROM StateInfos ) select [Hexcode] ,[DisplayName] ,[Code] ,[Original
阅读全文
摘要：查询存在某个属性的记录 db.getCollection('Machine').find({LineCapacityCount:{ $exists: true }}) 删除Machine文档中 Parameter对象的STD::STATUS属性， {} 表示所有 {"multi":true} 表示多
阅读全文
摘要：记录一下~ 你们应该用不到~ 1 --当一个班次时间内同时加工多种产品时，生产节拍=（零件1节拍*班次内零件1设备实际产量+零件2节拍*班次内零件2设备实际产量+...+）/（班次内零件1设备实际产量+班次内零件2设备实际产量+...+） 2 with 3 --1、依据公式计算各个零件实际产量及（零
阅读全文
摘要：-- 查看日志空间占用率DBCC SQLPERF ( LOGSPACE) SELECT TOP 10 st.text AS SQL_Full --父级完整语句 ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statemen
阅读全文
摘要：update [CutterStates] set DeviceGroupId = ( select mdg.DeviceGroupId from Machines m join MachineDeviceGroups mdg on m.id = mdg.MachineId where m.id =
阅读全文
摘要：字符集排序规则 --修改排序规则 ALTER DATABASE [WIMIDNC] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; ALTER DATABASE [WIMIDNC] COLLATE Chinese_PRC_CI_AS; ALTER DATABASE
阅读全文
摘要：概述 尽可能的让数据库处于可用状态。 提供高可用解决方案要考虑的因素 1、RTO（Recovery Time Objective）允许的脱机时间， 2、RPO（Recovery Point Objective）允许的数据丢失量 RTO和PRO统称为 SLA（Service Level Agremen
阅读全文
摘要：with T1 as ( SELECT ROW_NUMBER() over (PARTITION By MachineCode order by ABS(DATEDIFF(MILLISECOND,'2019-12-10 13:21:32.162' ,[Time]))) as rowid,* FROM
阅读全文
摘要：求时间字段与输入时间点的时间差j绝对值，最后取最小时间差 SELECT min(ABS(DATEDIFF(MILLISECOND,'2019-12-18 14:11:00.000' ,s.Time))) AS diff FROM EnergyConsumptions AS s
阅读全文
摘要：1 --查看被缓存的查询计划 2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 3 SELECT TOP 20 4 st.text AS [SQL] 5 , cp.cacheobjtype 6 , cp...
阅读全文
