CREATETRIGGER trgAfterInsert_UpdateUDFs
ON YourTargetTable
AFTERINSERTASBEGIN-- 禁用触发器的行为以避免递归触发SET NOCOUNT ON;-- 更新目标表中的 UDF004 和 UDF005 字段UPDATE t
SET
UDF004 = b.ProductName,
UDF005 = b.ProductStandard
FROM YourTargetTable t
INNERJOIN inserted i ON t.ID = i.ID
INNERJOIN Base_Product b ON i.MatCode = b.ProductCode
WHERE i.REGRULE ='MatRegSN';END;
创建聚集索引(NCLUSTERED用于分组排序)或非聚集索引(NONCLUSTERED)
CREATENONCLUSTEREDINDEX IX_FIT_METER_INFO_SERIAL_NO_DESC
ON FIT_METER_INFO (SERIAL_NO DESC)
两表中相同的项目名称来更新 Table1 中的项目编号
UPDATE Table1
SET Table1.ProjectNumber = Table2.NewProjectNumber
FROM Table1
INNERJOIN Table2 ON Table1.ProjectName = Table2.ProjectName;
WITHtempAS(SELECT'01'AS vouchertype,
td.DOC_NO AS cordercode,
t.UDF001 AS cwhcode,
t.CreateDate AS ddate,
t.ID AS ccode,
t.SUPPLIER_CODE AS cvencode,0AS states,
t.CreateDate,
t.DOC_NO AS mes_code,
ROW_NUMBER()OVER(PARTITIONBY t.ID ORDERBY t.CreateDate)AS rn
FROM
T_ToERP_Main t
INNERJOIN T_ToERP_MBody td ON t.Id = td.ID
WHERE XTYPE ='purchase'AND AuditState ='N'AND CreateDate >'2024-12-08 15:06:00')SELECT*FROMtempWHERE rn =1;--保留排序出的第一条记录
Sqlserver/Mysql用表标识列删除重复项
Mysql用表标识列删除重复项
想基于某列(或几列)的值来决定保留哪一行(比如总是保留ID最小的那一行)
DELETE t1 FROM your_table t1
JOIN(SELECTMIN(id)as min_id, column_with_duplicates
FROM your_table
GROUPBY column_with_duplicates
HAVINGCOUNT(*)>1) t2 ON t1.column_with_duplicates = t2.column_with_duplicates AND t1.id <> t2.min_id;
sqlserver用表标识列删除重复项
对于支持窗口函数的数据库系统(如SQL Server、PostgreSQL、Oracle等)
可以使用 ROW_NUMBER() 分配每个重复记录的行号,然后删除行号大于1的记录。
WITH CTE AS(SELECT*, ROW_NUMBER()OVER(PARTITIONBY column_with_duplicates ORDERBY id)as rn
FROM your_table
)SELECT*FROM CTE WHERE rn >1;-- 查询要删除的DELETEFROM CTE WHERE rn >1;--保留第一个
case条件逻辑控制结构
常用于翻译字段不同的结果
CASE case_value
WHEN when_value THEN statement_list
ELSE statement_list
ENDas 别名